在二维 VBA 中循环遍历 Excel 中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1836873/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Looping through cells in Excel in two dimensions VBA
提问by Psycho_Penguin
I'm trying to loop through a set of cells using VBA in a worksheet and check if they contain any data. My spreadsheet is like:
我试图在工作表中使用 VBA 遍历一组单元格并检查它们是否包含任何数据。我的电子表格是这样的：
__A_____B_____C_____D____E_____F____G
1| 3122 -1 -1 3243
2| -1 -1 -1 3243 1 1 1
3| -1 -1 -1 3255 1
5| 4232 2132 -1 3259
6| 7544 1333 324 3259
7| -1 -1 -1 3259 1 2 1
8| -1 -1 -1 3259 1 1 1
9| -1 -1 -1 3267
10| 2121 222 -1 3267
I want to get rid of the rows that don't have any data in columns E F and G but I'm not sure how to loop through the rows and columns. I have seen many instructions for looping down a column but I can't find anything on how to loop in two dimensions checking the cells for data.
我想摆脱 EF 和 G 列中没有任何数据的行，但我不确定如何遍历行和列。我已经看到很多关于循环列的说明，但我找不到任何关于如何在二维中循环检查单元格数据的信息。
Thanks
谢谢
回答by mechanical_meat
The basic idea of looping over rows and columns is that you need two forloops.
遍历行和列的基本思想是您需要两个for循环。
The first loops over rows, the second over columns.
第一个循环在行上，第二个在列上循环。
I don't use VBA enough to remember how rows get deleted, but if you loop backwards (as in the code below) you should never lose track of which row you're deleting.
我使用 VBA 并不足以记住行是如何被删除的，但是如果向后循环（如下面的代码所示），则永远不会忘记要删除的行。
The following code should work for your purposes (although it begs for refactoring!):
Edit: thanks to barrowcfor the correction.
以下代码应该适合您的目的（尽管它需要重构！）：
编辑：感谢barrowc的更正。
Sub remove_some_rows()
Dim i As Long
Dim j As Long
Dim current_cell As Object
Dim beg_row As Long
Dim end_row As Long
Dim beg_col As Long
Dim end_col As Long
beg_row = 1
end_row = 10
beg_col = 1
end_col = 7
Dim empty_col_counter As Integer
For i = end_row To beg_row Step -1
empty_col_counter = 0
For j = end_col To beg_col Step -1
Set current_cell = ThisWorkbook.ActiveSheet.Cells(i, j)
If j > 4 And current_cell.Value = "" Then
empty_col_counter = empty_col_counter + 1
End If
Next j
If empty_col_counter = 3 Then
current_cell.EntireRow.Select
Selection.Delete
End If
Next i
End Sub
回答by barrowc
This should work:
这应该有效：
Sub main()
Dim maxRow As Integer
Dim currentRow As Integer
With Worksheets("Sheet1")
maxRow = .Range("A1").CurrentRegion.Rows.Count
Dim i As Integer
' Start at the bottom and work upwards
For i = maxRow To 1 Step -1
' 5 represents column E, 6 is column F and 7 is column G
If (.Cells(i, 5).Value = "" And .Cells(i, 6).Value = "" And _
.Cells(i, 7).Value = "") Then
.Rows(i).Delete
End If
Next i
End With
End Sub
Because there are only three columns to check, it's easy to just use Andto join the three checks together. In a more complex situation, a nested Forloop as in Adam Bernier's answer would be better
因为只有三列要检查，所以很容易And将三个检查连接在一起。在更复杂的情况下，For亚当伯尼尔的答案中的嵌套循环会更好
