Excel VBA incrementing columns

Question

I am trying to combine multiple columns of varying lengths into 1 column on another sheet to allow for vlookups off this column.

I am able to increment down through the first column easily, but I am having trouble moving on to the next column.

Below is the code that I was working with. I am trying to use the cells identification to increment the column number but I am getting an application define or object define error 1004.

Sub TestTwo()
   Sheets("Transposed").Activate
   Dim row As Double

   row = 3

  'Do Until Sheets("Transposed").Range("B" & row).Value = ""
   Do Until Sheets("Transposed").Range(Cells(row, "B")).Value = ""

  Sheets("OneList").Range("B" & row - 1).Value = Sheets("Transposed").Range("B" & row).Value
row = row + 1

Loop


End Sub

The first do until line is commented out because I am trying to get the cells identifier to work before trying to increment.

What am I doing wrong?

Solution 1

Range() is looking for either an address or a range of cells. Use the Cells() by itself:

Do Until Sheets("Transposed").Cells(row, "B").Value = ""

Range does not like a single Cells() reference.

Solution 2

Scott is exactly correct (+1). But, I would approach this problem a bit differently than you with For loops. That way you can be more precise, and keep track of what everything does if you need to scale up

Option Explicit
Sub CombineColumns()
    Dim sourceColumns As Long
    Dim lastRow As Long
    Dim combinedRow As Long
    combinedRow = 1
    Dim sourceRows As Long

    For sourceColumns = 2 To 4 'B, C, D or whatever your range is
        lastRow = Cells(Rows.Count, sourceColumns).End(xlUp).Row
        For sourceRows = 1 To lastRow
            Sheet2.Cells(combinedRow, 1) = Cells(sourceRows, sourceColumns)
            combinedRow = combinedRow + 1
        Next
     Next sourceColumns
End Sub

Comments

[user2957915]

Thank you Scott. It was so frustrating because I knew it was something simple but couldn't figure out how to address the issue. Everything is working as intended now!