VBA: Delete all rows that contain certain values in a specific column

[Remi909]

Hi,

Im trying to create a VBA code that would loop through Column B to check if:

If cells in column B have either "Printer" or "N/A" to delete the entire row. There are about 10,000 rows in column B of which 5833 rows at random have either "Printer" or "N/A". Here's the code I currently have but I keep getting an error "Run time error '13' Type mismatch" when ran.

Sub Clean_Data()

    Dim WB As Workbook
    Dim WSR As Worksheet
    Dim I As Long
    Dim Rng As Range, Cell As Range

    Set WB = ActiveWorkbook
    Set WSR = WB.Worksheets("Equip")
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))

For Each Cell In Rng
    
    If Cell = "Printer" or Cell = "N/A" Then  
        Cell.EntireRow.Delete
    End If

Next Cell

End Sub

Please help. Thank you!

 

[Joe4]

@Joe4, I had tried that and if I type #N/A into a cell and then run that code it error out on that line with Type 13 mismatch error.
That is why I ended up testing for that in a line on its own.

OK, I did not test it out, so I will take your word for it!

Remi, based on that, you should just be able to use the last version of code Alex posted.

 

[Remi909]

@Joe4 I also tried that and got that and also got that error. @Alex Blakenburg's code worked for me - Is there a way for it to be faster? (Just asking for my knowledge)

 

[Joe4]

@Joe4 I also tried that and got that and also got that error. @Alex Blakenburg's code worked for me - Is there a way for it to be faster? (Just asking for my knowledge)

How long does it take now?

Alex already added the lines to suppress calculations and screen updating to speed it up.
A different method which might be faster is to use Filters, but I don't know if you can filter on errors (#N/A). You may need to clean that up first, if you wanted to pursue that option.

 

[Remi909]

How long does it take now?

Alex already added the lines to suppress calculations and screen updating to speed it up.
A different method which might be faster is to use Filters, but I don't know if you can filter on errors (#N/A). You may need to clean that up first, if you wanted to pursue that option.

Around 1 minute. I have tried filtering column B for just the rows that have "#N/A" and it only shows those rows. Would be intrigued to know if the different method would be faster in comparison.

 

[Joe4]

Around 1 minute. I have tried filtering column B for just the rows that have "#N/A" and it only shows those rows. Would be intrigued to know if the different method would be faster in comparison.

Can you filter it to just show those rows and the "Printer" rows?

If so, I have some code that will delete the unhidden rows:

Public Sub DeleteUnHiddenRows()
'   Deletes all unhidden rows except for the header (first row only)

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Exit sub if no data to delete data (only header visible)
    If lr = 1 Then Exit Sub

'   Delete unhidden data
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

End Sub

You can get the code you need to apply the filters by turning on the Macro Recorder and recording yourself applying those filters.
Then you should be able to put those two codes together to do this.

 

[Remi909]

Can you filter it to just show those rows and the "Printer" rows?

If so, I have some code that will delete the unhidden rows:

Public Sub DeleteUnHiddenRows()
'   Deletes all unhidden rows except for the header (first row only)

    Dim lr As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Exit sub if no data to delete data (only header visible)
    If lr = 1 Then Exit Sub

'   Delete unhidden data
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

End Sub

You can get the code you need to apply the filters by turning on the Macro Recorder and recording yourself applying those filters.
Then you should be able to put those two codes together to do this.

That worked like a charm! Thank you so much for that, much appreciated!

 

[Joe4]

You are welcome!
Glad I was able to help.

Yes, it is usually best to avoid loops in VBA code whenever possible. For a small amount of rows, it usually is not a big deal But if you have a lot of data rows, it can really take a long time to loop through them all.