combining multiple columns into one column - enhancements
 

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