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]

Are the values of "N/A" in the sheet literal text values of "N/A", or is it the "#N/A" error returned in some Excel formulas?

 

[Alex Blakenburg]

The below is just correcting the issue you are having.
When you delete rows you need to start from the bottom and work your way up. If you work top down then everytime you delete a row the next row you are about to reference moves up and throws out your referencing.

I think this will run far too slow on 10k records and also if it doesn't delete N/A you will need to address Joe's question.

Sub Clean_Data_mod()

    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 I = Rng.Count To 1 Step -1
        If Rng.Cells(I) = "Printer" Or Rng.Cells(I) = "N/A" Then
            Rng.Cells(I).EntireRow.Delete
        End If    
    Next I

End Sub

 

[Joe4]

A few things to note, that I saw in your original code, and looks like Alex may have unintentionally carried down.

You are setting Workbook and Worksheet variables, but neither are referenced in setting the "Rng" range.
So, that range will be set to whatever is the active sheet at the time the code runs. If you meant to include Workbook and Worksheet references in that range, you will need to add them in.

Also, you can speed up the current code by suppressing calculations and screen updating while the code is running, i.e. put this before your "For" loop code:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then by putting this after the loop ends (after the "Next I" line):

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

 

[Remi909]

Are the values of "N/A" in the sheet literal text values of "N/A", or is it the "#N/A" error returned in some Excel formulas?

Apologies I missed the #. Its "#N/A"

 

[Joe4]

Apologies I missed the #. Its "#N/A"

It is using preferrable to correct errors rather than deal with them.
What is the formula in those cells that returns that error?

 

[Remi909]

It is using preferrable to correct errors rather than deal with them.
What is the formula in those cells that returns that error?

The sheet in question was copied from a master sheet. I've tried checking what the original formula was but only shows "#N/A". I just need to clean the copied sheet and keep the data that's relevant.

 

[Alex Blakenburg]

I suggest you go with whatever Joe advises but since I am login off for the night, this is where I got to.

Sub Clean_Data_mod()

    Dim WB As Workbook
    Dim WSR As Worksheet
    Dim I As Long
    Dim Rng As Range
  
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

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

    For I = Rng.Count To 1 Step -1
        If Rng.Cells(I).Text = "#N/A" Then
             Rng.Cells(I).EntireRow.Delete
        ElseIf Rng.Cells(I) = "Printer" Then
            Debug.Print Rng.Cells(I).Address
            Rng.Cells(I).EntireRow.Delete
        End If
    Next I
  
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Joe4]

The sheet in question was copied from a master sheet. I've tried checking what the original formula was but only shows "#N/A". I just need to clean the copied sheet and keep the data that's relevant.

OK, if the value is now hard-coded, try this slight variation to Alex's code:

Sub Clean_Data_mod()

    Dim WB As Workbook
    Dim WSR As Worksheet
    Dim I As Long
    Dim Rng As Range
  
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

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

    For I = Rng.Count To 1 Step -1
        If Rng.Cells(I) = "#N/A" Or Rng.Cells(I) = "Printer" Then
            Rng.Cells(I).EntireRow.Delete
        End If
    Next I
  
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Alex Blakenburg]

@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.