adding lines of code to a macro to delete rows

[childofthe1980s]

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

 

[Mike H]

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

 

[Rick Rothstein]

Since the word row in Column A that the word "Grand" is in was put there by
your code, then you already know the row number for it... just store that
**row number** in a variable at the same time you put the word "Grand" into
its cell, then at the end of your code, you can execute this statement to
clear that row downward...

Range(RowWithGrandVariable & ":" & Rows.Count).Clear

Of course, use your actual variable's name for the example variable name of
RowWithGrandVariable that I used above.

 

[childofthe1980s]

Nope....didn't work.

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

 

[Mike H]

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]

Again, per my posting, I cannot rely on an exact row number.

 

[Rick Rothstein]

Again, per my posting, I didn't say to rely on an exact row number... what I
said was to remember the row number that **your code** used when it placed
the word "Grand" into whatever cell it placed the word Grand into. Your code
does do the placing of the word Grand into a cell, right? Just so you
understand our problems in trying to answer your question... 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.

 

[OssieMac]

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

 

[OssieMac]

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

 

[Rick Rothstein]

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).

 

[Mike H]

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

 

[Harald Staff]

Sub test()
Dim R As Long, RL As Long
RL = ActiveSheet.UsedRange.Rows.Count
R = Columns(1).Find(What:="Grand", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Range("A" & R & ":A" & RL).EntireRow.Delete
End Sub

HTH. Best wishes Harald