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

    Addressing Merged Cells (97+)

    Excel sees merged cells still as separate cells, so how do you 'grab' a range of adjacent merged cells? This is the best I could come up with:

    Sub UnMerge()
    Dim rngR As Range, rngCell As Range, rngActvCell As Range
    Set rngActvCell = ActiveCell
    Set rngR = Intersect(ActiveSheet.UsedRange, Selection)
    Application.ScreenUpdating = False
    If Not rngR Is Nothing Then
    For Each rngCell In rngR
    If rngCell.MergeCells Then
    'Excel sees merged cells still as separate cells,
    'so how do you specify a range of adjacent merged cells?
    <font color=red>rngCell.Select 'grabs the merged area
    Set rngCell = Range(Selection.Address) 'sets the merged area as a range</font color=red>
    With rngCell
    .MergeCells = False
    If .HorizontalAlignment = xlCenter Then _
    .HorizontalAlignment = xlCenterAcrossSelection
    End With
    End If
    Next rngCell
    End If
    rngActvCell.Activate
    Set rngActvCell = Nothing
    Set rngR = Nothing
    Set rngCell = Nothing
    Application.ScreenUpdating = False
    End Sub

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Addressing Merged Cells (97+)

    Hi John,
    I think Activecell.mergearea is what you're after. It returns the merged range of a given cell or the cell itself if it's not part of a merged range. E.g.:
    <pre> With ActiveCell.MergeArea
    .MergeCells = False
    .HorizontalAlignment = xlCenterAcrossSelection
    End With
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addressing Merged Cells (97+)

    playing slightly further:

    Sub SheetDeMerge()
    Dim ocell As range
    Dim ocells As range
    For Each ocell In ActiveSheet.UsedRange
    If ocell.MergeCells Then
    Set ocells = ocell.MergeArea
    ocell.MergeArea.MergeCells = False
    ocells.HorizontalAlignment = xlCenterAcrossSelection
    End If
    Next
    Set ocell = Nothing
    Set ocells = Nothing
    End Sub

    though if the merged range is more than two rows deep this may lead to unexpected results in terms of the formatting.

  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: Addressing Merged Cells (97+)

    Thanks, guys, MergeArea did the trick. FWIW I ended up with

    Sub UnMerge()
    Dim rngR As Range, rngCell As Range
    ' I use the following syntax because I can specify the whole sheet or just an area I want to operate on
    Set rngR = Intersect(ActiveSheet.UsedRange, Selection)
    Application.ScreenUpdating = False
    If Not rngR Is Nothing Then
    For Each rngCell In rngR
    If rngCell.MergeCells Then
    With rngCell.MergeArea
    .MergeCells = False
    .HorizontalAlignment = xlCenterAcrossSelection
    End With
    End If
    Next rngCell
    End If
    Set rngR = Nothing
    Set rngCell = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addressing Merged Cells (97+)

    There is also ASAP Utilities which has the option:

    Format|Unmerge cells in selection

    It's a free download from:

    http://asap-utilities.com

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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