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

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Hello, dennisly!

Maybe something like:
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,496
Office Version
  1. 365
Platform
  1. Windows
.. 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

Well-known Member
Joined
Feb 20, 2004
Messages
1,070

ADVERTISEMENT

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
 

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070

ADVERTISEMENT

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

Board Regular
Joined
Aug 26, 2005
Messages
165
This should work:
Code:
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

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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

Board Regular
Joined
Aug 26, 2005
Messages
165
This may do what you need:
Code:
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.
 

Forum statistics

Threads
1,136,995
Messages
5,679,037
Members
419,801
Latest member
spinjector

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top