Combining Multiple Columns into one column without any blank cells
Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 42

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

  1. #21
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by norulen View Post
    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).
    HTH, Peter
    Please test any code on a copy of your workbook.

  2. #22
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #23
    New Member
    Join Date
    Dec 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #24
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #25
    New Member
    Join Date
    Dec 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #26
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #27
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Quote Originally Posted by mdesroc View Post
    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. #28
    New Member
    Join Date
    Dec 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #29
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by mdesroc View Post
    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  10. #30
    New Member
    Join Date
    Dec 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks, definitely working now.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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