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