Delete #N/A

rollingzep

Board Regular
Joined
Nov 18, 2013
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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


1663349041448.png


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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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

Board Regular
Joined
Nov 18, 2013
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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

1663351417202.png


1663351417202.png
 

cmowla

Well-known Member
Joined
Sep 21, 2021
Messages
536
Office Version
  1. 365
Platform
  1. Windows
Here are comments explaining why your code didn't work (and corrections/and more explanations):

VBA 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")

'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

Board Regular
Joined
Nov 18, 2013
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Here are comments explaining why your code didn't work (and corrections/and more explanations):

VBA 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")

'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

Well-known Member
Joined
Sep 21, 2021
Messages
536
Office Version
  1. 365
Platform
  1. Windows
@mumps method IS most definitely faster. But if your sheet is protected, it can prevent .SpecialCells from working.
 

rollingzep

Board Regular
Joined
Nov 18, 2013
Messages
134
Office Version
  1. 365
Platform
  1. Windows
That's what is puzzling since my WS is not protected
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
Mumps code should be
VBA Code:
ws.Columns("AU").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
 
Solution

rollingzep

Board Regular
Joined
Nov 18, 2013
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 21, 2021
Messages
536
Office Version
  1. 365
Platform
  1. Windows
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.)
 

Forum statistics

Threads
1,181,615
Messages
5,930,962
Members
436,767
Latest member
Langaws

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top