Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...