Thread: Combining Multiple Columns into one column without any blank cells Thanks: 0 Likes: 0

1. Re: Combining Multiple Columns into one column without any blank cells

Originally Posted by norulen
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).

2. Re: Combining Multiple Columns into one column without any blank cells

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

3. Re: Combining Multiple Columns into one column without any blank cells

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.

4. Re: Combining Multiple Columns into one column without any blank cells

For me to run a 60 rows data it took less than one second. I dont really know how big your data is

5. Re: Combining Multiple Columns into one column without any blank cells

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?

6. Re: Combining Multiple Columns into one column without any blank cells

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```

7. Re: Combining Multiple Columns into one column without any blank cells

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

Originally Posted by mdesroc
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?

8. Re: Combining Multiple Columns into one column without any blank cells

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?

9. Re: Combining Multiple Columns into one column without any blank cells

Originally Posted by mdesroc
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```

10. Re: Combining Multiple Columns into one column without any blank cells

Thanks, definitely working now.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•