Delete Specific Text, with unknown blank space

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
528
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
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,640
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
528
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,671
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
528
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
528
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,671
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,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

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