想要一次性删除多个工作表中的所有#N/A [英] Want to delete all #N/A from the multiple sheet in one go

问题描述

需要帮助.

我正在使用以下代码，在这里我想删除整行包含#N/A的内容，尽管我要删除，但它会一行一行地删除，这很耗时. 我希望它选择所有#N/A并在范围内一次性删除.

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

推荐答案

如果要使用VBA解决方案，请尝试此操作.这不会删除循环中的行.

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

这篇关于想要一次性删除多个工作表中的所有#N/A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！