How do I write a loop in VBA that will delete a row if a cell in a range and the cell next to it both equal a constant?

Question

[This is in Excel 2007]

In other words, the loop will cycle through all the active cells in a one-column range (rngAddressName) and, if the cell in the range AND the cell directly to the left of it contain the string "#N/A", then it will delete that row.

Unfortunately, nothing I have tried has had any actual effect. Here is my best go at it:

i = 1
For counter = 1 To rngSC2A.Rows.Count
Contents = rngSC2A.Cells(i).Value
If Contents = "#N/A" Then
If rngAddressName.Cells(i).CellOffset(0, -1).Value = "#N/A" Then
rngAddressName.Cells(i).EntireRow.Delete
Else
End If
Else
i = i + 1
End If
Next

But this doesn't seem to find any rows with the conditions satisfied (even though such rows exist in the worksheet). I think it might have something to do with the fact that I am looking in the Cell.Value, but I am not sure.

When you are deleting rows, use a reverse loop. Also instead of `.Value`, try `.Text` — Siddharth Rout, Sep 16 '13 at 13:22
Here is an [example](http://stackoverflow.com/questions/18820728/excel-macro-repetitive-if-and-else) of Reverse Looping — Siddharth Rout, Sep 16 '13 at 13:23

score 2 · Answer 1 · answered Sep 16 '13 at 13:48

2

You can autofilter your range, delete any rows that meet your criteria, then turn the autofilter off. This is a much more efficient approach than looping.

The example below works on columns A and B in Sheet1. Modify the variables to reference the range and sheet in your workbook.

Sub DeleteDoubleNA()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:B" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter field:=1, Criteria1:="#N/A"
        .AutoFilter field:=2, Criteria1:="#N/A"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

answered Sep 16 '13 at 13:48

Jon Crowell

21,695
14
89
110

1

+ 1 :) I forgot abt that.. Now that you have posted this, I remember, posting a similar answer sometime ago. This definitely is the best way to go! – Siddharth Rout Sep 16 '13 at 14:01
Thanks, I will implement this. As it turns out I won't actually need the double filter starting next quarter, so it will be a much simpler case of filtering out the one cell in each row. – user2784067 Sep 16 '13 at 14:32
For some reason this has trouble deleting the first row if evry single row satisfies the conditions (ie, if all the cells in the range contain #N/A, this procedure won't delete the very first row). – user2784067 Sep 16 '13 at 16:11
Actually I fixed this myself - it was due to the offset property. I changed that to 0,0 (since I will no longer need to check for two columns) and it now erases everything with #N/A – user2784067 Sep 16 '13 at 16:35
The offset property in my example prevents the header row from being deleted. If you don't have one, you can do what you did or simply remove the .offset from the code altogether. – Jon Crowell Sep 16 '13 at 18:37
Ok the weird thing is, with the offset it will execute properly, except the header row isn't filtered/deleted. But if I change the offset, it will not function properly - it deletes absolutely everything, even the rows which do not fulfill the deletion criteria – user2784067 Sep 17 '13 at 13:23

score 0 · Answer 2 · answered Dec 21 '16 at 23:08

This is a different take on on the excellent answer posted by @Jon Crowell.

If you use an Excel table, you can use the table's ListObject to get the data range which automatically excludes the header and footer rows.

This avoids the sometimes incorrect calculation search for a last row.

You also want to clear any pre-existing filters on the data so that you don't overlook any rows.

Dim myTable As Object
Set myTable = ActiveSheet.ListObjects(1) ' Works if worksheet contains only one table

' Clear pre-existing filters on the table
myTable.AutoFilter.ShowAllData

' Filter the table
With myTable.DataBodyRange
    .AutoFilter field:=1, Criteria1:="#N/A"
    .AutoFilter field:=2, Criteria1:="#N/A"
End With

' Delete visible cells in the filtered table
myTable.DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

' Clear filters on the table
myTable.AutoFilter.ShowAllData

The (1) in ListObjects(1) is the first (in my case only) table in the worksheet. DataBodyRange refers to the data range of that table excluding header and footer rows.

How do I write a loop in VBA that will delete a row if a cell in a range and the cell next to it both equal a constant?

2 Answers2