looping question

  • Thread starter Thread starter luke
  • Start date Start date
L

luke

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 said:
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.
Click to expand...
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
 
thanks.

both methods work although they don't account for blank cells as the
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 give
me something to help me understand this sort of looping with ranges an
using counters etc...

thanks again, luk
 
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
 
Back
Top