vba Excel VBA循环行直到空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31477319/
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
Excel VBA Loop Rows Until Empty Cell
提问by Thomas
I have an excel document with some plain text in a row. A1:A5 contains text, then several houndred rows down, there's another few rows with text. Cells between are empty. I've set up a Do Untilloop which is supposed to copy cells with text, and then stop when an empty cell appears. My loop counts&copies 136 cells includingthe 5 with text. So my question is why? The bottom line: Helloends up on line 136, and then there's a huge gap of empty cells until next area with text. Do the 131 white cells contain any hidden formating causing this? I've tried "Clear Formats" and "Clear All" Code-snippet found below:
我有一个 excel 文档，一行中有一些纯文本。A1:A5 包含文本，然后向下几行，还有几行带有文本。之间的单元格为空。我已经设置了一个Do Until循环，它应该复制带有文本的单元格，然后在出现空单元格时停止。我的循环计数并复制 136 个单元格，包括 5 个带有文本的单元格。所以我的问题是为什么？底线：你好在第 136行结束，然后有一个巨大的空白单元格，直到下一个带有文本的区域。131 个白色单元格是否包含导致此问题的任何隐藏格式？我试过下面找到的“清除格式”和“清除所有”代码片段：
Sub CopyTags_Click()
Dim assets As Workbook, test As Workbook
Dim x As Integer, y As Integer
Set assets = Workbooks.Open("file-path.xlsx")
Set test = Workbooks.Open("File-path.xlsx")
x = 1
y = 1
Do Until assets.Worksheets(1).Range("A" & x) = ""
test.Worksheets(1).Range("A" & y) = assets.Worksheets(1).Range("A" & x)
x = x + 1
y = y + 1
Loop
test.Worksheets(1).Range("A" & x).Value = "Hello"
End Sub
Ive also tried using vbNullStringinstead of " "
我也试过使用vbNullString代替“”
回答by
Use a For Next Statementterminating in the last used cell in column A. Only increment yif there has been a value found and transferred and let the For ... Next increment x.
使用For Next 语句在 A 列中最后使用的单元格中终止。仅在找到并传输值时才增加y并让 For ... Next 增加x。
Sub CopyTags_Click()
Dim assets As Workbook, test As Workbook
Dim x As Long, y As Long
Set assets = Workbooks.Open("file-path.xlsx")
Set test = Workbooks.Open("File-path.xlsx")
x = 1
y = 1
with assets.Worksheets(1)
for x = 1 to .cells(rows.count, 1).end(xlup).row
if cbool(len(.Range("A" & x).value2)) then
test.Worksheets(1).Range("A" & y) = assets.Worksheets(1).Range("A" & x)
y = y + 1
end if
next x
test.Worksheets(1).Range("A" & y).Value = "Hello"
end with
End Sub
