Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Merge (VBA (Excel 97))

    How can I use VBA to merge multiple cells in Excel?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Merge (VBA (Excel 97))

    At it's simplest:

    Selection.MergeCells = True

    You can also use a range:

    Thisworkbook.WorkSheets("Sheet1").Range("A14").MergeCells = True

    or range name:

    Thisworkbook.WorkSheets("Sheet1").Range("namedrang e").MergeCells = True

    in place of Selection.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Merge (VBA (Excel 97))

    Thanks.

    Would you need to center text differently in merged cells?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Merge (VBA (Excel 97))

    With merged cells, you would use 'Center' rather than 'Center Across Selection'. While the macro recorder records highly inefficient code, this is a good example of where using the macro recorder shows you all the Properties so you can pick and choose which you need:

    With Selection
    .MergeCells = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter

    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With

    (Not to mention it also uses the With Statement, which is an efficent way to set a number of Properties for a single Object.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Merge (VBA (Excel 97))

    What am I doing wrong here? (the cells do not merge)

    range("a39:d39").activate
    set thesecells = activecell
    with thesecells
    .mergecells = true
    .value = "text"
    .horizontalalignment = xlcenter
    endwith

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Merge (VBA (Excel 97))

    This works:

    With Range("A3939")
    .MergeCells = True
    .Value = "text"
    .HorizontalAlignment = xlCenter
    End With

    As a good coding habit, I'd fully qualify the parent object of the Range, such as:

    With ThisWorkbook.Worksheets("Sheet1").Range("A3939")
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: VBA Merge (VBA (Excel 97))

    John has already posted a solution which should work but I think I know why your code wasn't working. I would be surprised if the activecell can contain more than one cell. Normally if you select a range, there is multiple cells selected but only one active cell.

    If this is the case then asking to merge a single cell would be unlikely to work.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Merge (VBA (Excel 97))

    You are exactly right Andrew, until the cells are merged, the ActiveCell is the top left cell of the selected range, so when he set ActiveCell, it deselected the range. I actually didn't get into the reason for failure because the code was inefficient. (After the cells are merged, the returned address is still the top left cell in the range, but the entire merged area treated as the activecell.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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