Combining Multiple Columns into one column without any blank cells

mdesroc

New Member
Joined
Dec 8, 2012
Messages
18
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
I copied his macro code exactly as it was listed in his post. Is there something else I need to add?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top