excel - Want to delete all #N/A from the multiple sheet in one go

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

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