想要一次性删除多个工作表中的所有#N/A [英] Want to delete all #N/A from the multiple sheet in one go
本文介绍了想要一次性删除多个工作表中的所有#N/A的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
需要帮助.
我正在使用以下代码,在这里我想删除整行包含#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屋!
查看全文