Delete Specific Text, with unknown blank space

[nniedzielski]

Hi guys-

I am having some trouble with some code. I run the below code to delete rows if Column B has either D000001 or D00000L8. The code runs, and some of the rows will get deleted, and if i keep running it more and more will delete, but for some reason my code will not run to the bottom and get all of them, this particular sheet has 4108 rows. I did notice that some of the values have a space or even two after them and thought maybe that was the problem?

Option Compare Text

Dim finalrow As Long
Dim m As Long
Dim t As Long
Dim trim As Long

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For m = 2 To finalrow
        If Cells(m, 2).Value = "D0000010" Or Cells(m, 2).Value = "D00000L8" Then
            Cells(m, 2).EntireRow.Delete
        End If
    Next m
End Sub

as always, thank you for any help

 

[Fluff]

How about

Sub nniedzielski()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 2, "D0000010*", xlOr, "D00000L8*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

When deleting rows in a loop as you were it's always best to loop from bottom up.

 

[jasonb75]

First of all, you need to loop backwards when deleting rows. If you don't, when a row is deleted, the next row (that moves up to take place of the deleted row) will be missed.

Are there any rows which may contain the criteria as a partial string and which should not be deleted?

 

[nniedzielski]

i cant believe i forgot about bottom up deleting, thank you Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[nniedzielski]

How about

Sub nniedzielski()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 2, "D0000010*", xlOr, "D00000L8*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

When deleting rows in a loop as you were it's always best to loop from bottom up.

your code works, but i had to click the button to run the sub two dozen times to get through the whole worksheet, i cant explain why it isnt going through all the data.

 

[nniedzielski]

How about

Sub nniedzielski()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 2, "D0000010*", xlOr, "D00000L8*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

When deleting rows in a loop as you were it's always best to loop from bottom up.

as info, the worksheet has many blank rows, maybe this is why the autofilter is not grabbing everything?

 

[Fluff]

the worksheet has many blank rows,

That's exactly why it didn't work
Try it like this

Sub nniedzielski()
   With ActiveSheet
      .Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 2, "D0000010*", xlOr, "D00000L8*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub