Need to Determine Range, and Execute a Loop Based on it

[meganrhae]

Excel 2007 on Windows XP

For a macro I am writing, I need to start in a certain cell and find the number of non-blank cells going down, then using this count execute a simple copy and paste loop. However I am new to the macro world and am struggling with the loop concept at least in this context.

For example:

Starting in A11, I need to know how many cells going down are filled until it reaches a blank cell. Say its 5.
Then I need it to copy from A11 and paste to A48, then A12 to A51, then A13 to A54, etc. 5 times.

Along with this, I thought about trying the .End(x1Up).Row action but am not sure if it will work:
I have cells filled in A11 up to A16, then there are blank cells, then more filled in cells that need to be ignored. Does the .End(x1Up).Row action look from the bottom or does it go to the end of a dataset and move up?

Thanks for your help!

 

[JoeMo]

The .End(xlUp).Row finds the last occupied row in a column. That's not what you want. Try .End(xlDown) to find the last occupied cell below some starting cell. For example, if the starting cell is A11 then:

Range("A11").end(xlDown).Row

Returns the row number that is one less than the row number of the first blank cell below A11. So if A11:A16 are all filled and A17 is blank, then this would return 16.