使用 VBA 遍历 Excel 中的填充行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24377197/
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
Iterating through populated rows in Excel using VBA
提问by Hambone
So I am trying to iterate through a worksheet in an Excel spreadsheet using VBA. I want to iterate through each row, and then through each column, and despite googling, I can't actually find an intuitive way to do this.
所以我试图使用 VBA 遍历 Excel 电子表格中的工作表。我想遍历每一行，然后遍历每一列，尽管使用谷歌搜索，但我实际上找不到一种直观的方法来做到这一点。
I'm assuming that the first cell of a row must be populated, if its not, then that must be the end. I can enforce this
我假设必须填充一行的第一个单元格，如果没有，则必须是结尾。我可以强制执行
My current approach is to iterate through the rows, then try and get the value of the first cell, but I can't figure it out. I've come across some questions here and elsewhere that use ranges and such, but nothing that helps me write code.
我目前的方法是遍历行，然后尝试获取第一个单元格的值，但我无法弄清楚。我在这里和其他地方遇到了一些使用范围等的问题，但没有任何帮助我编写代码的问题。
The current approach is:
目前的做法是：
Set sh = ActiveSheet
RowCount = 0
For Each rw In sh.Rows
'If Row.Cells(1, 1).Value = "" Then Exit For
RowCount = RowCount + 1
Next rw
MsgBox (RowCount)
Now when I run this, I get some huge number, which is wrong as the table only has about 25 rows. I commented the first line out as it wasn't working.
现在当我运行这个时，我得到了一些巨大的数字，这是错误的，因为表只有大约 25 行。我评论了第一行，因为它不起作用。
What can I change in the first line in the For Loop to correctly break when it finds a row where the first cell is empty?
当找到第一个单元格为空的行时，我可以在 For 循环的第一行更改什么以正确中断？
回答by AjV Jsy
For the benefit of anyone searching for similar, see worksheet .UsedRange,
e.g. ? ActiveSheet.UsedRange.Rows.Count
and loops such asFor Each loopRow in Sheets(1).UsedRange.Rows: Print loopRow.Row: Next
为了任何搜索类似的人的利益，请参阅 worksheet .UsedRange，
例如? ActiveSheet.UsedRange.Rows.Count
和循环，例如For Each loopRow in Sheets(1).UsedRange.Rows: Print loopRow.Row: Next
回答by Hambone
It looks like you just hard-coded the row and column; otherwise, a couple of small tweaks, and I think you're there:
看起来您只是对行和列进行了硬编码；否则，进行一些小调整，我认为您在那里：
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer
RowCount = 0
Set sh = ActiveSheet
For Each rw In sh.Rows
If sh.Cells(rw.Row, 1).Value = "" Then
Exit For
End If
RowCount = RowCount + 1
Next rw
MsgBox (RowCount)
回答by Portland Runner
I'm going to make a couple of assumptions in my answer. I'm assuming your data starts in A1 and there are no empty cells in the first column of each row that has data.
我将在我的回答中做出几个假设。我假设您的数据从 A1 开始，并且每行的第一列中没有空单元格有数据。
This code will:
此代码将：
- Find the last row in column A that has data
- Loop through each row
- Find the last column in current row with data
- Loop through each cell in current row up to last column found.
- 查找 A 列中包含数据的最后一行
- 遍历每一行
- 查找包含数据的当前行中的最后一列
- 循环遍历当前行中的每个单元格，直到找到的最后一列。
This is not a fast method but will iterate through each one individually as you suggested is your intention.
这不是一种快速方法，但会按照您的意图单独迭代每个方法。
Sub iterateThroughAll()
ScreenUpdating = False
Dim wks As Worksheet
Set wks = ActiveSheet
Dim rowRange As Range
Dim colRange As Range
Dim LastCol As Long
Dim LastRow As Long
LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
Set rowRange = wks.Range("A1:A" & LastRow)
'Loop through each row
For Each rrow In rowRange
'Find Last column in current row
LastCol = wks.Cells(rrow, wks.Columns.Count).End(xlToLeft).Column
Set colRange = wks.Range(wks.Cells(rrow, 1), wks.Cells(rrow, LastCol))
'Loop through all cells in row up to last col
For Each cell In colRange
'Do something to each cell
Debug.Print (cell.Value)
Next cell
Next rrow
ScreenUpdating = True
End Sub
