M
markx
Hello everybody,
Today, I've found (on one of the excel newsgroups) a macro "combining
multiple columns into one column", posted initially (probably) by Dave
Peterson or Bob Phillips.
The macro makes the basic work, however I would need some additional
enhancements:
- my initial page contains formulas, so I would need to convert these data
into "paste special/values".
- I've noticed that when I put "ctrl + down arrow" on the columns, il goes
further than the last cell with value (in fact, it goes always until the row
143), so it should also be resolved somehow...
, because without this,
there will be a lot of "free" space between the "real" end of one column and
the start of the next one...
- some columns contain blank cells in the middle: what should I add to the
code (as an option) if I want to elimitate all the blank cells in the new,
combined, column?
Many thanks for your help on this...
Mark
=============================
Sub OneColumn()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''
Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim idx As Integer
Set ws = ActiveWorkbook.ActiveSheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets.Add
.Name = "Alldata"
End With
idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate
For colndx = 1 To ilastcol
ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
Set myRng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myRng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next
Sheets("Alldata").Rows("1:1").EntireRow.Delete
End Sub
Today, I've found (on one of the excel newsgroups) a macro "combining
multiple columns into one column", posted initially (probably) by Dave
Peterson or Bob Phillips.
The macro makes the basic work, however I would need some additional
enhancements:
- my initial page contains formulas, so I would need to convert these data
into "paste special/values".
- I've noticed that when I put "ctrl + down arrow" on the columns, il goes
further than the last cell with value (in fact, it goes always until the row
143), so it should also be resolved somehow...
there will be a lot of "free" space between the "real" end of one column and
the start of the next one...
- some columns contain blank cells in the middle: what should I add to the
code (as an option) if I want to elimitate all the blank cells in the new,
combined, column?
Many thanks for your help on this...
Mark
=============================
Sub OneColumn()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''
Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim idx As Integer
Set ws = ActiveWorkbook.ActiveSheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets.Add
.Name = "Alldata"
End With
idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate
For colndx = 1 To ilastcol
ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
Set myRng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myRng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next
Sheets("Alldata").Rows("1:1").EntireRow.Delete
End Sub