multiple sections of rows to delete with VBA

Question

Below is the code I was using to set a starting point and end point of rows that needed to be deleted. This section is found a couple hundred times throughout the spread sheet. If I press F8 through the loop its fine. But if I hit F5 it will do a couple on its own before freezing and giving me an error. Is there a better way to do this?

Dim BeginDLT As Range
Dim EndDLT As Range


wbk.Application.Visible = True

'-------Delete Rows from Sheet Table 1---------

 Const strFindStart As String = "Report generated on:*"
 Set BeginDLT = wbk.Sheets("Table 1").Range("A:BU").Find(What:=strFindStart)
 
Const strFindLast As String = "Comments"
 Set EndDLT = wbk.Sheets("Table 1").Range("A:BU").Find(What:=strFindLast)
 
'-----reworking loop-------

Do Until BeginDLT.Row = 0
    If BeginDLT.Row <> 0 Then
        wbk.Sheets("Table 1").Rows(BeginDLT.Row & ":" & EndDLT.Row + 1).EntireRow.Delete
        Set BeginDLT = wbk.Sheets("Table 1").Range("A:BU").Find(What:=strFindStart)
        Set EndDLT = wbk.Sheets("Table 1").Range("A:BU").Find(What:=strFindLast)
    End If
Loop

`BeginDLT.Row = 0`... this will never be the case. There's no row `0`. — BigBen, Jun 18 '20 at 20:30
so should it be = to nothing? or not at all? thanks for commenting. and appreciate the help. im been learning VBA for all of 10 days now. — jmmVBANewb, Jun 18 '20 at 20:51
Untested, but maybe `Do Until Application.CountIf(wbk.Sheets("Table 1").Range("A:BU"), strFindStart) = 0`. — BigBen, Jun 18 '20 at 21:09
is there a way to find all the sections first and then do a delete instead of doing it one by one? — jmmVBANewb, Jun 19 '20 at 13:05
thats cool. any idea how i can write that? and explain it? would really appreciate — jmmVBANewb, Jun 19 '20 at 17:23
https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba is an example. — BigBen, Jun 19 '20 at 17:38

multiple sections of rows to delete with VBA

0 Answers0