hi Kevinz,

How many rows are in the spreadsheet?
If there are not many rows & it only has to be done once the quickest
approach would have been to do it manually!
In terms of "teaching a man to fish", the manual approach could be
recorded into a macro & adapted from there - as below:
recorded code:
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A14").Select
ActiveSheet.Paste

This can be adapted as follows but b/c I can't tell if you want the
complete row copied in rows 2 to 500 or just the info in some cells eg
A2:IV500 or A2:A500 I'll show you both...

Sub *CopyingBlanksBelowPopulatedCells*()
Dim bottomOfLastCopiedSection As Long
Repeat:
bottomOfLastCopiedSection = Selection.End(xlDown).Row
If bottomOfLastCopiedSection = rows.Count Then
MsgBox "all copying except last section complete. Please copy this
section manually."
Exit Sub
Else
ActiveCell.Range("A1").Copy Range(Selection,
Selection.End(xlDown).Offset(RowOffset:=-1))
Selection.End(xlDown).Activate
End If
GoTo Repeat
End Sub

Sub *CopyingBlanksBelowPopulatedRows*()
Dim bottomOfLastCopiedSection As Long
Repeat:
bottomOfLastCopiedSection = Selection.End(xlDown).Row
If bottomOfLastCopiedSection = rows.Count Then
MsgBox "all copying except last section complete. Please copy this
section manually."
Exit Sub
Else
ActiveCell.Range("A1").EntireRow.Copy Range(Selection,
Selection.End(xlDown).Offset(RowOffset:=-1))
Selection.End(xlDown).Activate
End If
GoTo Repeat
End Sub

There will be tidy ways of doing this but I don't know them & I can't
tell from your post how to recognise the end of used area which is why
I have finished it with a message to do the last section manually.
(fyi, a solution will probably involve checking the intersection of the
used range with the active cell).

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


kevinz Wrote:
This post was EXACTLY what I was looking for.. Although I am pretty new
to excel and am having some problems.

I did it to run but it is not going to the first blank cell, is it
going to the first blank cell at the bottom and copying and filling.
Is there a way to change that?

Basically what I have and need to do is..
Row1 = "Blah"
Then the next 500 rows under that column are empty.

Row 501 = "SomethingElse" Then blank rows until whatever..

I need something to fill in 2-500 with "Blah"

Then 502-whatever with "SomethingElse"

I hope that takes sense, from reading the post about this.. That is
what it is suppose to do but I can't get it to work. I just jumps to
the very bottom and starts and skips all the blank rows above it.

Any help would be so greatly appreciated, I have been messing with this
for hours and I need to get it done tonight..


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524172