Results 1 to 9 of 9

Thread: Merge rows

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Merge rows

    In an Excel file I've inherited, there are blocks of 14 and sometimes 15 rows that I need merged into 1 row.
    Excel doesn't allow this merge to happen (well, you can merge, but all of the data is lost except the top-most item. I wonder why they do that?!).

    I suspect there must be some VB code that might allow me to select a block of cells and then merge them into one?

    Any help would be appreciated. Thanks in advance.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Could you post an example showing what the result of the merge should look like?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Sample.
    Clip0001.jpg

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Here's a UDF that will do what I think you want.
    Code:
    Option Explicit
    
    Public Function zMergeCells(rngSource As Range, rngDest As Range) As String
    
       Dim rngCurCell As Range
       
       zMergeCells = ""
       
       For Each rngCurCell In rngSource
          If Trim(rngCurCell.Value) = "" Then
          Else
            zMergeCells = zMergeCells & " " & rngCurCell.Value
          End If
       Next rngCurCell
       
    End Function
    Place a call to the function in the destination cell, e.g. =zMergeCells(A3:A10,A1)
    Mergecells.PNG

    Sample workbook attached.

    I forgot to mention you can Paste values over the formulas when you're done if you want to delete the source rows. Just paste before the delete.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    This is almost what I need. It seems that you've concatenated the values in the rows. But, even with a wrap and merge, each row doesn't fall on a separate line in the merged cell. Maybe I need a loop that puts a new line character after each line of text???

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    New and Improved version!
    Code:
    Option Explicit
    
    Public Function zMergeCells(rngSource As Range, rngDest As Range) As String
    
       Dim rngCurCell As Range
       
       zMergeCells = ""
       
       For Each rngCurCell In rngSource
          If Trim(rngCurCell.Value) = "" Then
          Else
            If zMergeCells <> "" Then
              zMergeCells = zMergeCells & vbCrLf & rngCurCell.Value
            Else
              zMergeCells = rngCurCell.Value
            End If
          End If
       Next rngCurCell
       
    End Function
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think that's it...that line feed, as I thought, seems to have done the trick. I'm not sure what your "destination" does.
    I called the UDF from, say, C20, but used a second argument of G1 but it didn't matter. The merge still when where the UDF was called.
    What am I missing?

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Side note: I taught a course once and kept saying "carriage return" as in CrLf and some (YOUNG) person in the class said "what's a carriage return?" Some of us used to work on those things called typewriters.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    You're not missing a thing. I should have gotten rid of the second argument. I was thinking ahead of myself and included it when I didn't really need it.
    Just change the Function line to Public Function zMergeCells(rngSource As Range) As String
    and drop the second argument from the formula...Sorry.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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