VBA code to delete entire rows if #N/A were found simultaneously in columns A, B & C

[Nikit Strygin]

Hi!

I'm trying to make a VBA which would allow me to delete entire rows on the specific worksheets if #N/A were found simultaneously in columns A, B & C. For now I'm stucked with error "1004 object defined error". I would be very thankful for your help! Thank you in advance

Sub CleanNA()
Dim ws As Variant
Dim x As Long
Dim columnsrange As Variant

ws = Array("Sheet 1", "Sheet 2", "Sheet 3")
Set columnsrange = Range("A:C")
    For x = 0 To UBound(ws)
        If ActiveWorkbook.Sheets(ws(x)).Range(columnsrange).Cells.Value = CVErr(xlErrNA) Then
            ActiveWorkbook.Sheets(ws(x)).Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Clear
            ActiveWorkbook.Sheets(ws(x)).Cells.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Clear
        End If
    Next
Application.ScreenUpdating = True
MsgBox ("#N/A were successfully cleaned")

End Sub

 

[DanteAmor]

Hi & welcome to MrExcel.

How about:

Sub clearrows()
  Dim ws As Variant
  Dim x As Long
  
  ws = Array("Sheet 1", "Sheet 2", "Sheet 3")
  For x = 0 To UBound(ws)
    On Error Resume Next
    Sheets(ws(x)).UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Clear
  Next
End Sub

 

[Nikit Strygin]

Hi & welcome to MrExcel.

How about:

Sub clearrows()
  Dim ws As Variant
  Dim x As Long
 
  ws = Array("Sheet 1", "Sheet 2", "Sheet 3")
  For x = 0 To UBound(ws)
    On Error Resume Next
    Sheets(ws(x)).UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Clear
  Next
End Sub

Thank you for your answer! But it seems to me that this is not what I meant. Let me try to explain it more precisely. I need a code which will look at row values in columns A, B and C and if the values in the row are simultaneously in 3 columns A,B and C equal to the "#N/A" then the entire row will be deleted, but if in the row there is text it won't be cleared

 

[DanteAmor]

Try this

Sub clearrows()
  Dim ws As Variant
  Dim x As Long
  
  ws = Array("Sheet 1", "Sheet 2", "Sheet 3")
  For x = 0 To UBound(ws)
    With Sheets(ws(x))
      .Range("A:C").AutoFilter Field:=1, Criteria1:="#N/A"
      .Range("A:C").AutoFilter Field:=2, Criteria1:="#N/A"
      .Range("A:C").AutoFilter Field:=3, Criteria1:="#N/A"
      .AutoFilter.Range.Offset(1).Clear
      .ShowAllData
    End With
  Next
End Sub