Hi OssieMac

You may be correct but I don't think that's the issue

LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Will find the last row even if there are blanks. I think the issue with my
first code was poor coding. From my first post

LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Rows(FirstRow & ":" & LastRow).ClearContents

Both of those lines will fail if the active sheet doesn't contain the data

From my second post
LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
..Rows(FirstRow & ":" & LastRow).ClearContents

Note these lines are now inside the with statement for sheet2 so the active
sheet doesn't matter.

I think Rick hit the nail on the head when he makes the valid point

"we cannot see
your code (because you didn't post it), so we have to make guesses as to
what you have and what it is you are doing with it. If you could post your
code, that would make answering your question so much easier."

I doubt we will ever know now because both Rick & I appear to be off the
OP's Christmas card list but thanks for your comments. The OP may respond to
you and hopefully his/her problem will be resolved


Happy Christmas Ossiemac

Mike

"OssieMac" wrote:

I left a msgbox in my previously posted code that I was using during testing
and as an afterthought it is a good idea to include code to handle "Grand not
found" so that the code will not error if run twice or grand is missing for
any other reason. The following code is an improvement.

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)

If Not Marker Is Nothing Then
FirstRow = Marker.Row
LastRow = .Rows.Count
Else
MsgBox "Grand not found. No rows deleted"
Exit Sub
End If
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