Excel VBA循环行直到空单元格 [英] Excel VBA Loop Rows Until Empty Cell

问题描述

我有一个excel文件，其中连续有一些纯文本. A1:A5包含文本，然后向下拖动几行，还有几行包含文本.之间的单元格为空.我设置了一个Do Until循环，该循环应该复制带有文本的单元格，然后在出现空单元格时停止.我的循环计数并复制136个单元格，包括5个带有文本的单元格.所以我的问题是为什么?底线:你好在第136行结束，然后有大量空白单元格，直到下一个带有文本的区域. 131个白色单元格是否包含任何导致这种情况的隐藏格式?我尝试了下面的清除格式"和清除所有"代码段:

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 Until loop 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: Hello ends 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:

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

我也尝试使用vbNullString代替"

推荐答案

使用对于下一条语句" 终止于A列中最后使用的单元格.如果已经找到并传输了一个值，则仅递增 y ，并让For ... Next递增 x .

Use a For Next Statement terminating in the last used cell in column A. Only increment y if there has been a value found and transferred and let the For ... Next increment 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

这篇关于Excel VBA循环行直到空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！