Combining Multiple Columns into one column without any blank cells

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

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

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

    Default Combining Multiple Columns into one column without any blank cells

     
    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.

  2. #2
    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 Simple formula
    =B2&C2&D2
    If this isn what you are looking for let me know your input and output requirement for a sample data

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

    Let me clarify a bit, because I think my phrasing was confusing. I want it to list what is in column B first, and then after the last thing from column B, list the first from thing column C, and then after the last thing in Column C, the first thing in column D.
    MY issue is, I dont know how to do this when the number of cells in columns B, C and D change based user selections elsewhere in the spreadsheet.

  4. #4
    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 the first part my formula should take care of it but i am not clear on this part. I dont understand what you are requirement is. hopefully someone else is able to understand and help you on this
    Quote Originally Posted by mdesroc View Post
    MY issue is, I dont know how to do this when the number of cells in columns B, C and D change based user selections elsewhere in the spreadsheet.

  5. #5
    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
    1 Thread(s)

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

    Maybe

    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.

  6. #6
    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, maybe this will help clarify, I have data in column B, C, and D. The number of entries I have in each column changes. I want column A to look like this automatically

    B1
    B2
    C1
    C2
    C3
    D1
    D2

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

    May not be the shortest of the code but i am sure it ll work for you. (Assumption: First row of the sheet is header hence first row will not get copied. If required change B2,C2,D2 to B1,C1,D1
    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("B2", "B" & rcB).Copy
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("C2", "C" & rcC).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("D2", "D" & rcD).Copy
        Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
    End Sub

  8. #8
    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,

    It definitely copied all 3 columns, except it started the values from column C at cell A500 and column D at cell A1000 with a bunch of blanks in-between. Any way to fix that?

  9. #9
    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
    1 Thread(s)

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

    HTH, Peter
    Please test any code on a copy of your workbook.

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

      
    I copied his macro code exactly as it was listed in his post. Is there something else I need to add?

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
  •  

 

 
DMCA.com