Clever Geek Handbook

Combining data from multiple columns into one column

I have the following:
enter image description here


I am using @ BigBobby's answer to achieve this:

Sub MoveData()
Range("H5:H99").ClearContents
START_ROW = 5
START_COL = 1
STEP_COL = 2
OUTPUT_ROW = 5
OUTPUT_COL = 8
Limit_Col = 9

Row = START_ROW
Col = START_COL
Out_Row = OUTPUT_ROW
While Col < Limit_Col
    While Cells(Row, Col).Value <> ""
        Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
        Out_Row = Out_Row + 1
        Row = Row + 1
    Wend
    Row = START_ROW
    Col = Col + STEP_COL
Wend
End Sub


But as you can see, I expect to get the values ​​that appear after the empty cell in the columns. But this code can't pull out those cells highlighted in yellow.
How do I modify this code to pull out all data that might appear after one or more blank cells?

0


source to share


2 answers


Adjust this code:

While Cells(Row, Col).Value <> ""
    Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
    Out_Row = Out_Row + 1
    Row = Row + 1
Wend

For:

Do until row > Cells(65536, Col).End(xlUp).Row
    Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
    Out_Row = Out_Row + 1
    Row = Row + 1
Loop


This essentially checks if a row of the last row of data has passed, and if there is, it moves to the next column.

Edit

To avoid copying through empty cells use this:

    Do until row > Cells(65536, Col).End(xlUp).Row
        If Cells(Row, Col).Value <> "" then
            Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
            Out_Row = Out_Row + 1
        End If
        Row = Row + 1
    Loop
+1


source


The previous answer is close, but it will copy all whitespace as well. This code should do what you need:



Sub MoveData()

START_ROW = 5
START_COL = 1
STEP_COL = 2
OUTPUT_ROW = 5
OUTPUT_COL = 10

Row = START_ROW
Col = START_COL
Out_Row = OUTPUT_ROW
While Col < OUTPUT_COL
    While Row < ActiveSheet.UsedRange.Rows.Count
        If Cells(Row, Col).Value <> "" Then
            Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
            Out_Row = Out_Row + 1
        End If
        Row = Row + 1
    Wend
    Row = START_ROW
    Col = Col + STEP_COL
Wend
End Sub
+2


source






More articles:


All Articles