Want to delete all #N/A from the multiple sheet in

Question

Need help here.

I am using the below coding where i want to delete entire row contains #N/A, though i am bale to delete but it deletes one by one line which is time consuming. i want that it select all the #N/A and delete in one go with in the range.

Sub RemoveNA ()
Dim ws As Worksheet

For sh = 1 To Worksheets.Count

Set ws = Worksheet (sh)
ws.Activate 

LR= ws.Cells(Rows.count,"B").End(xlUp).Row

If ws.Name <> "Temp" Then

For i = LR To 2 Step -1

If Cells(i, "B").Text="#N/A" Then

Rows(i).EntireRows.Delete

End If

Next i

Next sh

End Sub

Answer 1

Do you really need VBA for this? Do you have any other errors in cells?

If you only have those #N/A errors how about just selecting your column/range and using ctrl+G --> Special --> select formula errors, then you can just right click delete all.

If you still want to use macro as you might to go through multiple sheets do Application.ScreenUpdating = False before you execute code and set it back to Application.ScreenUpdating = True when done or on error. It will greatly increase the execution time.

Answer 2

If you want a VBA solution then try this. This doesn't delete the rows in a loop.

Sub RemoveNA()
    Dim ws As Worksheet
    Dim delRange As Range
    Dim i As Long

    For Each ws In ThisWorkbook.Worksheets
        With ws
            If .Name <> "Temp" Then
                LR = .Cells(.Rows.Count, "B").End(xlUp).Row
                For i = 2 To LR
                    If .Range("B" & i).Text = "#N/A" Then
                        If delRange Is Nothing Then
                            Set delRange = .Rows(i)
                        Else
                            Set delRange = Union(delRange, .Rows(i))
                        End If
                    End If
                Next i
            End If
        End With

        If Not delRange Is Nothing Then
            delRange.Delete
            Set delRange = Nothing
        End If
    Next ws
End Sub