Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
"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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
formula question | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Link Question | Excel Worksheet Functions |