Macro to delete rows in excel where cells contain certain text

Question

I'm new to VBA and not very adept with the programming so I'm hoping for some help here.

Here's the thing:

I have an excel sheet where I need to scan the entire rows and delete rows where Column C and Column D have the text "NA" in the same row.

Thanks in advance.

Possible duplicate of [excel delete row if column contains value from to-remove-list](https://stackoverflow.com/questions/12393524/excel-delete-row-if-column-contains-value-from-to-remove-list) — ashleedawg, Jun 06 '18 at 11:49

JC Guidicelli · Answer 1 · 2018-06-06T12:26:50.233

1

Try this. You need to start loop from the end :

Sub test()


    Dim wb As Workbook
    Set wb = ThisWorkbook

    Dim ws As Worksheet
    Set ws = wb.Sheets("sh_test") 'edit your sheet name
    Dim lastrow As Long
    lastrow = ws.Range("C" & Rows.Count).End(xlUp).Row + 1 'maybe change letter of the column

    For i = lastrow To 2 Step -1
        If ws.Cells(i, 3).Value = "NA" And ws.Cells(i, 4).Value = "NA" Then
            ws.Rows(i).EntireRow.Delete     
        End If
    Next i 
End Sub

edited Jun 06 '18 at 12:26

answered Jun 06 '18 at 11:54

JC Guidicelli

1,296
7
16

"NA" is text not a blank cell – inadvisableguy Jun 06 '18 at 12:01
I edited the answer, is it work ? You can adjust the range to find last row (change column letter) and the end of loop (change to "2" for something else) – JC Guidicelli Jun 06 '18 at 12:02
1

Don't use `Integer` for row counting! Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA. – Pᴇʜ Jun 06 '18 at 12:07
I would use "UsedRange" to get the rows. "Sheets(1).UsedRange.Rows" – Sam Dean Jun 06 '18 at 12:08
@JC Guidicelli not really – inadvisableguy Jun 06 '18 at 12:09
@inadvisableguy what's wrong ? do you have error message ? – JC Guidicelli Jun 06 '18 at 12:11
No, somehow it deletes some other rows in the process as well. @TBoulz 's answer worked fine. Thanks though :) – inadvisableguy Jun 06 '18 at 12:15
@inadvisableguy I edited the answer, adjust variables sheet name – JC Guidicelli Jun 06 '18 at 12:17
Ok no problem ! – JC Guidicelli Jun 06 '18 at 12:17
@JCGuidicelli `lastrow` will be the last row of `ActiveSheet` you might want to move that behind your worksheet declaration and use `ws.Range…` and also declare `i as Long` too. – Pᴇʜ Jun 06 '18 at 12:25

score 1 · Accepted Answer · answered Jun 06 '18 at 12:05

Here is something I've used before. This applies filters, filters for NA in both column C and D, then removes those rows (excluding the header). Note that this will only remove the row if NA is in BOTH columns. If NA is in C but not in D then it will not remove the row.

'Applies filter to the columns. You will want to adjust your range.
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$6").AutoFilter Field:=3, Criteria1:="NA"
ActiveSheet.Range("$A$1:$D$6").AutoFilter Field:=4, Criteria1:="NA"

'Removes the rows that are displayed, except the header
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True

'Removes the filter
Cells.Select
Selection.AutoFilter

Hope that helps.

Macro to delete rows in excel where cells contain certain text

2 Answers2