code to delete rows after row containg "End of report&q

[dennisli]

Good morning,
Can you gurus provide a code to delete all the used rows after the row containing “End of report” in column A.
Thanks lot.
Dennis

 

[Macropheliac]

Hello, dennisly!

Maybe something like:

Sub Delete_Rows()
Dim Mycell As Range
Dim Tcell As Range

For Each Tcell In Range("A:A")
        If Tcell.Value = "End of report" Then
                Set Mycell = Tcell
        End If
Next Tcell

Do Until Mycell.Offset(1).Value = ""
        Mycell.Offset(1).EntireRow.Delete
Loop

End Sub

 

[Peter_SSs]

.. or this perhaps?
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()
    <SPAN style="color:#00007F">With</SPAN> Columns("A:A")
        .Find(What:="End of report", After:=Range("A1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("A" & ActiveCell.Row + 1 & ":A65536").EntireRow.Delete
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[dennisli]

Wonderful. Thanks lot.

 

[dennisli]

Sorry, I am back.
I just test the two codes and found both do not work well.
The first one with For Loop does not do the loop.
The code using Find function gets an error message: Run time error 448.
Can you guys fix it?
Dennis

 

[Macropheliac]

What is the text of the error message? Which line does it error on?

 

[dennisli]

For code with Find, the error message is: Run time error 448. Named arguments not found.
For the code with loop, it actually do not show any results.
By the way, before you make any correction, I should clarify my statement. Actually, there are lots of “End of report”. I just want to delete the rows after the first row containing “ End of report”.
Thanks lot.
Dennis

 

[Macropheliac]

This should work:

Sub Delete_Rows()
Dim Mycell As Range

With Range("A:A")
      Set Mycell = .Find(What:="End of report", After:=Range("A1"), LookIn:= _
        xlFormulas)
End With

Do Until Mycell.Offset(1).Value = ""
        Mycell.Offset(1).EntireRow.Delete
Loop

End Sub

 

[dennisli]

Hi, Macropheliac
Your code assumes that the code deals with continued nonempty cells in column A and ignores one situation: if some cells in column A are empty after the row containing “End of report” but there are lots of non blank cells after the empty cells.
Can you improve it a little?
Thanks lot.

 

[Macropheliac]

This may do what you need:

Sub Delete_Rows()
Dim Mycell As Range
Dim Tcell As Range

With Range("A:A")
      Set Mycell = .Find(What:="End of report", After:=Range("A1"), LookIn:= _
        xlFormulas)
End With

For Each Tcell In Range("mycell:A65536") 
       If Tcell.Value <> "" Then
                Tcell.EntireRow.ClearContents
        End If
Next Tcell

End Sub

If not, post back.