I still say the simplest solution is for the OP to have his macro remember
(in a variable) what the last line of "real" data was BEFORE the code starts
putting "junk" data in the rows below it. That way, no searching is required
at all... this single line of code would clean up all the "junk" data in one
fell swoop...

Range((LastRealDataRow + 1) & ":" & Rows.Count).Clear

The Clear operation should automatically stop at the last row of the
UsedRange, so I think using Rows.Count is sufficient (athough it might be
necessary to qualify the range references with a worksheet reference
depending on information the OP has not given us).

--
Rick (MVP - Excel)


"OssieMac" wrote in message
...
I would suggest that the reason the code failed is because there are blank
cells in column A between the last row of junk data and the word Grand and
it
has not correctly found the last row. Using the last row on the worksheet
will eliminate that possibility.

Sub Clear_Junk()
Dim FirstRow As Long, LastRow As Long
Dim Marker As Range

With Worksheets("Sheet1").Range("a:a") 'Change to suit
Set Marker = .Cells.Find(What:="Grand", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
MsgBox Marker.Address
FirstRow = Marker.Row
LastRow = .Rows.Count
End With
Rows(FirstRow & ":" & LastRow).ClearContents
End Sub


--
Regards,

OssieMac


"Mike H" wrote:

Nope....didn't work.

That's not a very good description of what went wrong!!

Try this modification
Sub Clear_Junk()
Dim FirstRow As Long, LastRow As Long
With Worksheets("Sheet2") 'Change to suit
With .Range("a:a")
Set Marker = .Cells.Find(What:="Grand", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
FirstRow = Marker.Row
.Rows(FirstRow & ":" & LastRow).ClearContents
End With
End Sub
Mike



"childofthe1980s" wrote:

Nope....didn't work.

"Mike H" wrote:

Hi,

How about this

Sub Clear_Junk()
Dim FirstRow As Long, LastRow As Long
With Worksheets("Sheet2") 'Change to suit
With .Range("a:a")
Set Marker = .Cells.Find(What:="Grand", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End With
FirstRow = Marker.Row
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Rows(FirstRow & ":" & LastRow).ClearContents
End Sub

Mike

"childofthe1980s" wrote:

Hello:

As the last step of a macro that I have written, I need all rows at
the end
of this spreadsheet to be deleted. (The rows at the end of the
spreadsheet
have bogus data, and I need to get rid of all of those rows and
leave just
rows of "real" data--long story short.)

Specifically, the row after the last row of "real" data is a cell
in column
A that simply contains the word "Grand". How do I put in code at
the end
that says "delete all rows at the end of this spreadsheet beginning
with this
last row that contains just the word 'Grand' at the end of column
A"?

At the moment, "Grand" is in cell A667. But, that is not going to
be the
case everytime that I run this macro for this data. Next time,
"Grand" could
be in cell A748, A901, A820, who knows?.......So, I cannot simply
put in code
that says "delete all rows at the end of this spreadsheet beginning
with the
row at cell A667".

Thanks!

childofthe1980s