Loop and Range questions

[gripper]

Hello,

I have a work sheet that I am trying to stack some items that is spread accross several columns into Column A but I am having a little issue putting the loop and range issue together. So I am look for some advise.

I am very week on Loops I am finding out.

What I simply need is this. How do you set a loop that will store a variable which is the row number. For instance in the first loop it will copy the content and paste to cell A1 then in the next loop it will then recognize that the next items need to be pasted in cell A100 then next to A200 and so on. I am having issues will making this variable when I know the column number but the row is the only part that needs to change as it works through.

I research and I know I need a For...Next Loop but I just cannot figure out the syntax for only moving the row down and not changing the column.

I am sure this is pretty elementary but if I could get a nudge in the right direction I would greatly appreciate it.

Thanks

 

[VoG]

That sounds straightforward enough. Exactly what do you want to copy and where should it be pasted?

 

[gripper]

I have most of the data manipulation already set up with code that does not require a loop but essentially I will be going from row H - QQ and copying the content in each row (as is) and moving it to Column A.

For instance Column H will copy (not cut and paste) to Cell A1. Then loop to copy and paste Column I to cell A100 then loop and copy J to A200 and so on. I know their will be gaps in the column A between the copies but that is fine I have a piece of code I used in the past to clean this up and condense as a final process in the code.

So basically the columns is H to QQ (no breaks) and the rows is column A cell 1A to whatever row in steps of 100.

Thanks

 

[VoG]

If I understand correctly try

Sub test()
Dim i As Long, j As Long
i = 1
For j = 8 To 459
    Cells(ActiveCell.Row, j).Copy Destination:=Cells(i, 1)
    i = i + 100
Next j
End Sub

 

[gripper]

I am working this into my procedure but I want to maybe get a quick lesson on this line of code

Cells(ActiveCell.Row, j).Copy Destination:=Cells(i, 1)
i = i + 100

what is the mechanics of this so I have a good understanding.

Thanks

 

[VoG]

Cells(ActiveCell.Row, j).Copy Destination:=Cells(i, 1) ' copy from activecell.row, column j to range("A"&i)
i = i + 100 'add 100 to i so the next row is 100 on from the previous

 

[gripper]

This work great! Thanks for the help