# Combining Multiple Columns into one column without any blank cells

#### mdesroc

##### New Member
I need to be able to combine 3 columns with ranges that may change into one column without any blank cells.

For example, I have columns B, C and D filled with data, but the number of cells in each of those columns will change based on user choices other places in the spreadsheet.

Is it possible to combine the data from B, C, D into Column A automatically and if so how would I go about doing that?

Thank you for any help or advice you can offer.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### norulen

##### Active Member
Try this Simple formula
=B2&C2&D2
If this isn what you are looking for let me know your input and output requirement for a sample data

#### mdesroc

##### New Member
Let me clarify a bit, because I think my phrasing was confusing. I want it to list what is in column B first, and then after the last thing from column B, list the first from thing column C, and then after the last thing in Column C, the first thing in column D.
MY issue is, I dont know how to do this when the number of cells in columns B, C and D change based user selections elsewhere in the spreadsheet.

#### norulen

##### Active Member
For the first part my formula should take care of it but i am not clear on this part. I dont understand what you are requirement is. hopefully someone else is able to understand and help you on this
MY issue is, I dont know how to do this when the number of cells in columns B, C and D change based user selections elsewhere in the spreadsheet.

#### VoG

##### Legend
Maybe

Code:
``````Sub test()
Dim LR As Long, i As Long
For i = 2 To 4
LR = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(1, i), Cells(LR, i)).Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub``````

#### mdesroc

##### New Member
Norulen, maybe this will help clarify, I have data in column B, C, and D. The number of entries I have in each column changes. I want column A to look like this automatically

B1
B2
C1
C2
C3
D1
D2

#### norulen

##### Active Member
May not be the shortest of the code but i am sure it ll work for you. (Assumption: First row of the sheet is header hence first row will not get copied. If required change B2,C2,D2 to B1,C1,D1
Code:
``````Sub Macro1()
rcB = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
rcC = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
rcD = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

Range("B2", "B" & rcB).Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C2", "C" & rcC).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2", "D" & rcD).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub``````

#### mdesroc

##### New Member
Norulen,

It definitely copied all 3 columns, except it started the values from column C at cell A500 and column D at cell A1000 with a bunch of blanks in-between. Any way to fix that?

#### mdesroc

##### New Member
I copied his macro code exactly as it was listed in his post. Is there something else I need to add?

1,101,774
Messages
5,482,846
Members
407,365
Latest member
Leah Ashley

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...