looping question
 

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


--
luke
------------------------------------------------------------------------
luke's Profile: http://www.excelforum.com/member.php...o&userid=21401
View this thread: http://www.excelforum.com/showthread...hreadid=375917

"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.

One way:-

For i = 2 To 4 'or howevermany columns you need
Range(Cells(1, i), Cells(5, i)).Copy
Range("A65536").End(xlUp).Offset(1, 0).Select 'selects the next
cell in A
ActiveCell.PasteSpecial
Range(Cells(1, i), Cells(5, i)).ClearContents
Next i

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

thanks.

both methods work although they don't account for blank cells as they
paste the data to the first blank cell in column A. But no worries,
i'll see what I can do to solve that. This has got me started and given
me something to help me understand this sort of looping with ranges and
using counters etc...

thanks again, luke


--
luke
------------------------------------------------------------------------
luke's Profile: http://www.excelforum.com/member.php...o&userid=21401
View this thread: http://www.excelforum.com/showthread...hreadid=375917

I misread your initial post - if you want to move exactly n rows:

Const nROWS As Long = 5
Dim i As Long

For i = 2 to Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, i).Resize(nROWS, 1).Copy _
Destination:=Cells((i - 1) * nROWS + 1, 1)
Next i


In article ,
luke wrote:

thanks.

both methods work although they don't account for blank cells as they
paste the data to the first blank cell in column A. But no worries,
i'll see what I can do to solve that. This has got me started and given
me something to help me understand this sort of looping with ranges and
using counters etc...

thanks again, luke