Excel VBA Loop Rows to empty cell
I have an excel document with some plain text in a row. A1: A5 contains text, then some blocked lines, some more lines of text. Cells between blank. I have set up a loop Do Until
that is to copy cells with text and then stop when an empty cell appears. My loop counts and copies 136 cells, including 5 with text. So why my question? Bottom line: Hello ends on line 136, and then there is a huge gap of blank cells until the next area of text. Do 131 white cells have a hidden formation causing this? I've tried the "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
I've also tried using vbNullString
instead of ""
source to share
Use for the next statement ending in the last used cell in column A. Only increment y, if a value was found and transferred, let 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
source to share