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

 

[dennisli]

This is the new code error message:

Run-time error “1004”
Method ‘Range’ of object’_Global’ failed

Thanks lot.

Dennis

 

[Macropheliac]

Okay, my mistake. Try this:

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.Offset(1), Range("A65536"))
       If Tcell.Value <> "" Then
                Tcell.EntireRow.ClearContents
        End If
Next Tcell

End Sub

 

[dennisli]

now it works. have a good afternoon.

 

[dennisli]

Sorry, Sir,
I am back again.
If there is no End of report in the range, the code gets an error message:

Run-time error ‘91’:
Object variable or with block variable not set.

What should I do?

Thanks lot.
Dennis

 

[Macropheliac]

Just change to this:

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

If Not Mycell Is Nothing Then
        For Each Tcell In Range(Mycell.Offset(1), Range("A65536"))
               If Tcell.Value <> "" Then
                        Tcell.EntireRow.ClearContents
                End If
        Next Tcell
Else
       MsgBox "No End of report found"
End If
End Sub