VBA：当行为空时退出循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14989936/
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
VBA: Exit For Loop when row is empty
提问by Momo
I'm using the following code to export rows to individual text files:
我正在使用以下代码将行导出到单个文本文件：
Sub export_Test()
Dim firstRow As Integer, lastRow As Integer, fileName As String
Dim myRow As Integer, myStr As String
firstRow = 10
lastRow = 29
For myRow = firstRow To lastRow
fileName = "C:\mallet\test\" & Cells(myRow, 1) & ".txt"
Open fileName For Append As #1
myStr = Cells(myRow, 2).Value
Print #1, myStr
Close #1
Next
End Sub
The problem is that this code is for a specific number of rows. I want to use this code for different data samples, so the number of rows in the excel file will vary and could number in the thousands. I need the lastRow variable to be set to an infinite number and exit the For Loop when it hits an empty row.
问题是此代码适用于特定数量的行。我想将此代码用于不同的数据样本，因此 excel 文件中的行数会有所不同，并且可能数以千计。我需要将 lastRow 变量设置为无限数，并在遇到空行时退出 For 循环。
回答by Peter Albert
This code will start in row 10 and run until it finds a blank cell in the second column. Note that I also shortened your code a bit (though it still does the same writing to a file):
此代码将从第 10 行开始并运行，直到在第二列中找到一个空白单元格。请注意，我还稍微缩短了您的代码（尽管它仍然对文件进行相同的写入）：
Sub export_Test()
Dim myRow As Long
myRow = 10
While Cells(myRow, 2).Value <> ""
Open "C:\mallet\test\" & Cells(myRow, 1) & ".txt" For Append As #1
Print #1, Cells(myRow, 2).Value
Close #1
myRow = myRow + 1
Wend
End Sub
回答by cardmagik
This is code from a project of mine that does exactly what you want - end with a blank value
这是我的一个项目中的代码，它完全符合您的要求 - 以空白值结尾
Sub export_Test()
Dim firstRow As Integer, lastRow As Integer, fileName As String
Dim myRow As Integer, myStr As String
firstRow = 10
myRow = firstRow
' Seed initial value
Cells(myRow, 1).Select
' Keep going until a blank cell is found
While Trim(ActiveCell.Value) <> ""
fileName = "C:\mallet\test\" & ActiveCell.Value & ".txt"
Open fileName For Append As #1
myStr = Cells(myRow, 2).Value
Print #1, myStr
Close #1
' Get the next value
myRow = myRow + 1
Cells(myRow, NameCol).Select
Wend
End Sub
