Delete #N/A

[rollingzep]

Hi,

I have a sheet wherein I do a Vlookup. For the values that are not found, it generates the #N/A error.

I am trying to delete those rows which have the #N/A error, but it is not deleting





This is my code. I call the Del_Rows in my main code by using Call Del_Rows

<code>
Sub Del_Rows()
Dim wbexcel As Workbook
Dim ws As Worksheet
Dim LR As Long, i As Long

Set wbexcel = ThisWorkbook
Set ws = wbexcel.Sheets("TSS Fails")

LR = ws.Range("AU2:AU" & Rows.Count).End(xlUp).Row
For i = LR To Step - 1
If Cells("AU2" & i).Text = "#N/A" Then Rows(i).EntireRow.Delete
Next i

End Sub

</code>

What am I doing wrong?

 

[mumps]

Try:

Sub Del_Rows()
    Dim wbexcel As Workbook, ws As Worksheet
    Set wbexcel = ThisWorkbook
    Set ws = wbexcel.Sheets("TSS Fails")
    ws.Columns("AU").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[rollingzep]

Try:

Sub Del_Rows()
    Dim wbexcel As Workbook, ws As Worksheet
    Set wbexcel = ThisWorkbook
    Set ws = wbexcel.Sheets("TSS Fails")
    ws.Columns("AU").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

I tried that and get

 

[cmowla]

Here are comments explaining why your code didn't work (and corrections/and more explanations):

Sub Del_Rows()

Dim wbexcel As Workbook
Dim ws As Worksheet

Dim LR As Long, i As Long
Set wbexcel = ThisWorkbook
Set ws = wbexcel.Sheets("TSS Fails")

'This doesn't work.
'LR = ws.Range("AU2:AU" & Rows.Count).End(xlUp).Row

'Use the following instead:
LR = ws.Cells(ws.Rows.Count, "AU").End(xlUp).Row

'This is incorrect syntax.
'Step comes AFTER the upper (well, in this case, lower bound).
'For i = LR To Step - 1
For i = LR To 1 Step -1
    'You have to put ws. before Cells("AU2"...) AND Rows(i) too.'
    'Not just Range(...)!  They all represent cells/ranges.
    'If Cells("AU2" & i).Text = "#N/A" Then Rows(i).EntireRow.Delete
  
    'And Cells("AU2"...) is also incorrect.
    'The syntax for Cells(...) is Cells(row#, column# or letter).
    If ws.Cells(i, "AU").Text = "#N/A" Then ws.Rows(i).EntireRow.Delete
Next i

End Sub

'And you don't need to set any objects to variables.
'So this sub could have been written like this to
'achieve the same result:
'Notice that there are STILL .'s in front of Cells, Rows, etc.
'With the WITH statement, it is saying (for line ##):
'LR = ThisWorkbook.Sheets("TSS Fails").Cells(ThisWorkbook.Sheets("TSS Fails").Rows.Count,"AU").End(xlup).Row
'It's like factoring in math.  Like how x^2+x = x(x+1).
Sub Del_Rows_2()

With ThisWorkbook.Sheets("TSS Fails")
    LR = .Cells(.Rows.Count, "AU").End(xlUp).Row '##
    For i = LR To 1 Step -1
        If .Cells(i, "AU").Text = "#N/A" Then .Rows(i).EntireRow.Delete
    Next i

End With

End Sub

I tried that and get

Is your sheet protected?

 

[rollingzep]

Here are comments explaining why your code didn't work (and corrections/and more explanations):

Sub Del_Rows()

Dim wbexcel As Workbook
Dim ws As Worksheet

Dim LR As Long, i As Long
Set wbexcel = ThisWorkbook
Set ws = wbexcel.Sheets("TSS Fails")

'This doesn't work.
'LR = ws.Range("AU2:AU" & Rows.Count).End(xlUp).Row

'Use the following instead:
LR = ws.Cells(ws.Rows.Count, "AU").End(xlUp).Row

'This is incorrect syntax.
'Step comes AFTER the upper (well, in this case, lower bound).
'For i = LR To Step - 1
For i = LR To 1 Step -1
    'You have to put ws. before Cells("AU2"...) AND Rows(i) too.'
    'Not just Range(...)!  They all represent cells/ranges.
    'If Cells("AU2" & i).Text = "#N/A" Then Rows(i).EntireRow.Delete
 
    'And Cells("AU2"...) is also incorrect.
    'The syntax for Cells(...) is Cells(row#, column# or letter).
    If ws.Cells(i, "AU").Text = "#N/A" Then ws.Rows(i).EntireRow.Delete
Next i

End Sub

'And you don't need to set any objects to variables.
'So this sub could have been written like this to
'achieve the same result:
'Notice that there are STILL .'s in front of Cells, Rows, etc.
'With the WITH statement, it is saying (for line ##):
'LR = ThisWorkbook.Sheets("TSS Fails").Cells(ThisWorkbook.Sheets("TSS Fails").Rows.Count,"AU").End(xlup).Row
'It's like factoring in math.  Like how x^2+x = x(x+1).
Sub Del_Rows_2()

With ThisWorkbook.Sheets("TSS Fails")
    LR = .Cells(.Rows.Count, "AU").End(xlUp).Row '##
    For i = LR To 1 Step -1
        If .Cells(i, "AU").Text = "#N/A" Then .Rows(i).EntireRow.Delete
    Next i

End With

End Sub

Is your sheet protected?

That worked although for a while I thought it was going through endless loop!

 

[cmowla]

@mumps method IS most definitely faster. But if your sheet is protected, it can prevent .SpecialCells from working.

 

[rollingzep]

That's what is puzzling since my WS is not protected

 

[Fluff]

Mumps code should be

ws.Columns("AU").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

 

[rollingzep]

ws.Columns("AU").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

Ah! I failed to notice the xlConstants rather than xlFormulas. It was much faster.
Thanks to you and mumps!

 

[cmowla]

I'm glad it worked for you. I hope you will also (when you have time) take the time to read over the comments/correct to your code. It's fundamental concepts in VBA that you need to understand to prevent similar things from happening again in the future. (And that post was technically answering the question of this thread.)