Delete Specific Text, with unknown blank space

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
525
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 8, 2016
Messages
525
Office Version
  1. 2019
Platform
  1. Windows
i cant believe i forgot about bottom up deleting, thank you Fluff
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
525
Office Version
  1. 2019
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Jan 8, 2016
Messages
525
Office Version
  1. 2019
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
the worksheet has many blank rows,
That's exactly why it didn't work
Try it like this
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,382
Messages
5,635,931
Members
416,888
Latest member
Mike66

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