It sounds like you want to use one of the techniques at Debra Dalgleish's site:
http://www.contextures.com/xlDataEntry02.html

Steph wrote:

I have a sheet with data in column A, but with blocks of blank cells
beneath. So for instance, Revenue is in cell A3. Expenses is in cell A45.
How can I programatically copy A3 down to A44, and then A45 down to the next
non-blank cell, and so forth until the last record in column B is reached?
Thanks for any help!

--

Dave Peterson