Move Different Columns of Data to Single Column.

[willik]

Hello,

I am new to VB with Excel 2002 and I am a lost goose right about now.

I am trying to move different columns of text data to a single column
i.e. Column E to column A. Col D to Col A. Col C to Col A. and Col
to Col A. Each column has different size rows. The columns and ro
sizes change from day to day, so I need a macro that will recogniz
different size columns and rows.

Today I have columns A through E with different length rows.

I am not sure how to identify the array size, since it will be changin
each time I export the data from another database. Once I have the dat
in Excel then I need to automatically move
( cut and paste ) or whatever, to populate column A with all the dat
from the other columns. I do not care how the data is stacked initiall
in column A. I can do a sort, in the macro, once the data is in colum
A.

I performed this task with the Excel VB recorder and came up th
following code for known cells or array size. My problem is that th
array size, i.e. column and row size will change each time I export ne
data from other database.

Sub MoveColumnsToA()
'
' MoveColumnsToA Macro
' Macro recorded 2/26/2004 by willik
'

'
Range("E1:E11").Select
Selection.Cut
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 61

 

[Tom Ogilvy]

Sub PutinColumnA()
Dim rng as Range, rng1 as Range
Dim sStr as String
Dim cell as Range, cell1 as Range
Set rng = cells.specialCells(xlCellTypeLastCell)
set rng = Range(Range("A1"),cells(rng.row,1))
for each cell in rng
set rng1 = cells(cell.row,"IV").End(xltoLeft)
sStr = ""
for each cell1 in Range(cell.offset(0,1),rng1)
if not isempty(cell1) then
sStr = sStr & trim(cell1.value)
end if
cell.Vaue = cell.Value & sStr
Range(cell.offset(0,1),rng1).ClearContents
Next
Next
End Sub

 

[willik]

To: Tom Ogilvy
Thank you for the resonse. I really appreciate the help. This worked
great, but I did not explain myself well enough. This is my fault. I
started a new thread with the my question written, hopegfully, in more
detail. The name of the new thread is

" Move data from different columns into one column, but different
rows. "

Thank you again for the help.

Regards, (willik)