Excel VBA Loop Rows Until Empty Cell

Question

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

Ive also tried using vbNullString instead of " "

Answer 1

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