Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 42

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

  1. #11
    MrExcel MVP 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

    No, the macro that I posted

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  2. #12
    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

    VoG,
    When I run it, its only filling in from column B and C

  3. #13
    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

    Are there blanks in the original data?? i.e. are there any blanks in B, C and D column

  4. #14
    MrExcel MVP 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

    For me it copied columns B, C and D - the results of my test

    Sheet1

     ABCD
    1 bcd
    2bbcd
    3bbcd
    4bb d
    5bb d
    6b  d
    7c  d
    8c  d
    9c  d
    10d  d
    11d   
    12d   
    13d   
    14d   
    15d   
    16d   
    17d   
    18d   
    19d   


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #15
    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

    there arent any blanks in the original data. Is there anything special I have to do to run the macro? I just have been hitting the macro tab and clicking run

  6. #16
    MrExcel MVP 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

    The sheet of interest must be selected before you run the code.
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #17
    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

    Ok, I did that. It definitely only copied column B and C for me

  8. #18
    MrExcel MVP 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

    Can you post a shot of your sheet, for example using Excel Jeanie How to
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #19
    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 i am sure it will work
    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("B1", "B" & rcB).Copy
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("C1", "C" & rcC).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("D1", "D" & rcD).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    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

  10. #20
    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

    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

    Quote Originally Posted by norulen View Post
    Try this i am sure it will work
    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("B1", "B" & rcB).Copy
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("C1", "C" & rcC).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("D1", "D" & rcD).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    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

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
  •