One way:

Dim rDest As Range
Dim rSource As Range

For Each rSource In Range(Cells(1, 2), _
Cells(1, Columns.Count).End(xlToLeft))
Set rDest = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
With rSource
.Resize(Cells(Rows.Count, .Column).End(xlUp).Row, 1).Copy _
Destination:=rDest
End With
Next rSource


In article ,
luke wrote:

I've used a few looping type macros until now with no problems. However,
I'm completely stumped when i need to get excel to count things for
me

I have 5 row of data in each column (for example). I want to get all
the data to be in column A each set below the next. so I need to refer
to B1:B5 and move it to A6:A10. Then repeat to move C1:C5 to A11:A15
etc..etc...

I need to move all 5 rows in each case regardless of content (number
letter, blank etc..)

I just come up against a brick wall when it comes to all this i=i+1
stuff, refering to ranges and telling excel to move the destination
cells down column A each step.

any help would be gratefully received. It may be me but I thought there
would be more info about loops. even the books i have don't cover this
sort of looping very comprehensively.

Luke