Combining Multiple Columns into one column without any blank cells

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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).
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

norulen

Active Member
Joined
Nov 30, 2012
Messages
389
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 :) :)
 

mdesroc

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

norulen

Active Member
Joined
Nov 30, 2012
Messages
389
For me to run a 60 rows data it took less than one second. I dont really know how big your data is
 

mdesroc

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

norulen

Active Member
Joined
Nov 30, 2012
Messages
389
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
 

norulen

Active Member
Joined
Nov 30, 2012
Messages
389
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?
 

mdesroc

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,850
Messages
5,483,303
Members
407,393
Latest member
GeorgeBrown

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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top