excel - 循环遍历整个工作簿并删除具有#N/A 的行
问题描述
我有一个 11 张工作簿。我需要删除所有工作表中出现#N/A 的行。我该怎么做呢?
我写了一些代码,见下文,它允许我只删除当我的 excel 文件打开到该特定选项卡时出现的 #N/A 行。它不会循环遍历整个工作簿。
Sub RemoveErrorsLoop()
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 2 To WS_Count
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Next I
End Sub
我想要的代码将遍历我的所有工作表并删除具有#N/A 的行。谢谢你的帮助!
解决方案
您只是忘记在循环中使用您的“I”:
如果您只是添加Sheets(I).
您的行将在工作簿的每一页中工作。
Sub RemoveErrorsLoop()
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Sheets(I).Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Next I
End Sub
当工作表中没有#N/A 时,我什至会添加一个错误处理程序以让代码正常工作。
For I = 1 To WS_Count
On Error Resume Next
Sheets(I).Cells.SpecialCells(xlCellTypeFormulas,xlErrors).EntireRow.Delete
On Error GoTo 0
Next I
推荐阅读
- javascript - Reject JavaScript promise
- android - startActivity intent is crashing on app upgrade in android 10 & above
- java - When I run springboot using springloaded, it throws exception,why it happens?
- elasticsearch - how to access go templated kubernetes secret in manifest
- database - Need to establish Database connectivity in DRL file
- r - 如何使R中igraph中的箭头线更短?
- reactjs - Handling continuous stream of logs using socket-io in react
- python - Bot在加入服务器时不会更改用户的昵称
- javascript - 如何使用 WebAssembly 和 JS 将命令行参数传递给 C 代码?
- sql - how to convert sql rows into columns with conditions