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.
 
Hey VOG, Can you have a look at this code and give me some tips to shorten or make it more compact. I know this is not the cleanest of the code but i would appreciate if you can give me some useful tip as i only have working knowledge macros :)

Hi, your code worked for me but look at how to loop through the columns as I did, avoiding repetitive code (also no selecting).
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Yaa i just went through it. Really interesting and short :)
Bdw your code is working on my sample also except the non blank part of it. Its copying data from all the three columns :)
Good one VOG :) :)
 
Upvote 0
norulen,

Thanks, It seems to do the job. Out of curiousity, how long does it take you to run it? When I run it, the row #s flash yellow for a while, and it looks like Excel is locking up. Im wondering if this is normal, or is there something weird going on with my version.
 
Upvote 0
For me to run a 60 rows data it took less than one second. I dont really know how big your data is
 
Upvote 0
on that one sheet it isnt all that big. Maybe 100 rows or so per column. Other pages in the workbook have 1000s of rows, but I only need the macro for the first worksheet. Will that change run time?
 
Upvote 0
Try this compact version which i incorporated from VOG Code. See if this takes less time
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
LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    For r = LR To 2 Step -1
        
        If ActiveSheet.Range("A" & r).Value = "" Then
            Range("A" & r).Delete Shift:=xlUp
            
        End If
  
     Next r
End Sub
 
Upvote 0
It should not affect the run time cos this macro will be running only on the activesheet.
There could be something wrong with the file. Why dont you copy the data to a new workbook and try if it takes lesser time

on that one sheet it isnt all that big. Maybe 100 rows or so per column. Other pages in the workbook have 1000s of rows, but I only need the macro for the first worksheet. Will that change run time?
 
Upvote 0
Is it possible the reason the macro is taking awhile to work is because the data Im copying is the result of a formula and not just text?
 
Upvote 0
Is it possible the reason the macro is taking awhile to work is because the data Im copying is the result of a formula and not just text?

Try this

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
    Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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