Delete entire row based on multiple cell criteria

[NKlawender]

I am trying to write a macro that will delete rows based on criteria in two columns. The columns contain written answers to survey questions. I want to delete the rows where both cells are blank, both cells say <Not defined>, both cells say na or n/a, both cells have just a period. Here is what I have written. The issue is that I get an error every time i try to run it, when I hit debug, the part in yellow is what is highlighted. I'm not sure what the issue is. Also, is there another way this could be done that may be faster? There are usually around 35,000 rows of data.

'Delete blank verbatim response rows
Dim LastA As Long
With Sheets("Combined Clean Files")
LastA = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For r = LastA To 2 Step -1
If .Cells(r, 11) = "" And .Cells(r, 12) = "" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "." And .Cells(r, 12) = "." Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "na" And .Cells(r, 12) = "na" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "n/a" And .Cells(r, 12) = "n/a" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "<Not defined>" And .Cells(r, 12) = "<Not defined>" Then .Cells(r, "A").EntireRow.Delete
Next r
End With
Application.ScreenUpdating = True


There is a little more to this code, but it is only opening and saving the files.
Thank you,
Norm

 

[breynolds0431]

I am trying to write a macro that will delete rows based on criteria in two columns. The columns contain written answers to survey questions. I want to delete the rows where both cells are blank, both cells say <Not defined>, both cells say na or n/a, both cells have just a period. Here is what I have written. The issue is that I get an error every time i try to run it, when I hit debug, the part in yellow is what is highlighted. I'm not sure what the issue is. Also, is there another way this could be done that may be faster? There are usually around 35,000 rows of data.

'Delete blank verbatim response rows
Dim LastA As Long
With Sheets("Combined Clean Files")
LastA = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For r = LastA To 2 Step -1
If .Cells(r, 11) = "" And .Cells(r, 12) = "" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "." And .Cells(r, 12) = "." Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "na" And .Cells(r, 12) = "na" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "n/a" And .Cells(r, 12) = "n/a" Then .Cells(r, "A").EntireRow.Delete
If .Cells(r, 11) = "<Not defined>" And .Cells(r, 12) = "<Not defined>" Then .Cells(r, "A").EntireRow.Delete
Next r
End With
Application.ScreenUpdating = True


There is a little more to this code, but it is only opening and saving the files.
Thank you,
Norm

Hi. What part highlights during debug? There's nothing highlighted in what you provided.

Also, you should put code in the VBA wrap. See below example.

'Delete blank verbatim response rows
Dim LastA As Long
With Sheets("Combined Clean Files")
LastA = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    For r = LastA To 2 Step -1
        If .Cells(r, 11) = "" And .Cells(r, 12) = "" Then .Cells(r, "A").EntireRow.Delete
        If .Cells(r, 11) = "." And .Cells(r, 12) = "." Then .Cells(r, "A").EntireRow.Delete
        If .Cells(r, 11) = "na" And .Cells(r, 12) = "na" Then .Cells(r, "A").EntireRow.Delete
        If .Cells(r, 11) = "n/a" And .Cells(r, 12) = "n/a" Then .Cells(r, "A").EntireRow.Delete
        If .Cells(r, 11) = "<Not defined>" And .Cells(r, 12) = "<Not defined>" Then .Cells(r, "A").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True

 

[NKlawender]

Sorry, I am having trouble with the add-in on my work computer. The part that is highlighting is the first If Then line.
I had this problem once before, but never found a solution.
Thanks,
Norm

 

[NKlawender]

Any idea if there would be a non-looping solution to this? I am still very new to VBA and am learning a ton from this and other sites.
Thanks

 

[breynolds0431]

Sorry, I am having trouble with the add-in on my work computer. The part that is highlighting is the first If Then line.
I had this problem once before, but never found a solution.
Thanks,
Norm

I received the below when I tested on a protected worksheet. Is your sheet password protected? If so, you'll have to .unprotect it before the loop and apply the .protect again after the loop.

If that isn't the issue, can you send the specific debug error you receive?

 

[NKlawender]

I received the below when I tested on a protected worksheet. Is your sheet password protected? If so, you'll have to .unprotect it before the loop and apply the .protect again after the loop.

If that isn't the issue, can you send the specific debug error you receive?

View attachment 71392

The workbook is not password protected.

 

[breynolds0431]

Do you have an cells with "#N/A". I tried adding a few of those to my test sheet and it gave me a runtime 13 error.

If you do have "#N/A" cells, I was able to replace them all with blanks prior to starting your loop. See below.

Sub testdeleterows()

'Delete blank verbatim response rows
Dim LastA As Long
With Sheets("Combined Clean Files")

.Cells.Replace "#N/A", "" 'Added to remove error cells

LastA = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    
    For r = LastA To 2 Step -1
          If .Cells(r, 11) = "" And .Cells(r, 12) = "" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "." And .Cells(r, 12) = "." Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "na" And .Cells(r, 12) = "na" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "n/a" And .Cells(r, 12) = "n/a" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "<Not defined>" And .Cells(r, 12) = "<Not defined>" Then .Cells(r, "A").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True

End Sub

 

[NKlawender]

Do you have an cells with "#N/A". I tried adding a few of those to my test sheet and it gave me a runtime 13 error.

If you do have "#N/A" cells, I was able to replace them all with blanks prior to starting your loop. See below.

Sub testdeleterows()

'Delete blank verbatim response rows
Dim LastA As Long
With Sheets("Combined Clean Files")

.Cells.Replace "#N/A", "" 'Added to remove error cells

LastA = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
   
    For r = LastA To 2 Step -1
          If .Cells(r, 11) = "" And .Cells(r, 12) = "" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "." And .Cells(r, 12) = "." Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "na" And .Cells(r, 12) = "na" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "n/a" And .Cells(r, 12) = "n/a" Then .Cells(r, "A").EntireRow.Delete
          If .Cells(r, 11) = "<Not defined>" And .Cells(r, 12) = "<Not defined>" Then .Cells(r, "A").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True

End Sub

That seemed to do it. Thank you!