Why range of cells aren't merging.

[Ark68]

I have this code:

        If Application.VLookup(FacB, wshfac.Range("A:H"), 6, False) = "N" Then
            With .Range("AB24:AE27")
                .Font.Color = RGB(224, 224, 224)
                '.Locked = True
            End With
        End If
        With .Range("AB24:AB24, AC24:AE24, AB25:AD25, AB26:AB26, AC26:AE26, AB27:AD27, AA27:AD27, AE27:AE27")
            .MergeCells = True
            .Locked = True
        End With

If the value in column 6 of wshfac.range("A:H") of the looked up value FacB = "N", which in my sample ase it does ...
The code in blue will change the font colour of the contents in range AB24:AE27 to grey, and the code in red will merge a series of cell ranges.

The merging of range AC24:AE24 and AC26:AE26 is not working. Everything else does. The sheet is not protected.

 

[Norie]

You can't merge multiple, non-contiguous ranges at the same time.

Try this.

Dim ar As Range

    For Each ar In .Range("AB24:AB24, AC24:AE24, AB25:AD25, AB26:AB26, AC26:AE26, AB27:AD27, AA27:AD27, AE27:AE27").Areas

        ar.MergeCells = True
        ar.Locked = True
    Next

 

[Scott Huish]

You can't merge multiple, non-contiguous ranges at the same time.

Sure you can, you just have to do it differently.

Sub test()
With Union(.Range("AB24:AB24"), .Range("AC24:AE24"), .Range("AB25:AD25"), .Range("AB26:AB26"), _
    .Range("AC26:AE26"), .Range("AB27:AD27"), .Range("AA27:AD27"), .Range("AE27:AE27"))
    .MergeCells = True
    .Locked = True
End With
End Sub

 

[Scott Huish]

Good point.

 

[Rick Rothstein]

Good point.

Actually, I just deleted my message (which is the one I think you were referring to in your reple). Why? Because when I ran your code, I realized he wasn't really merging single cells... he was actually merging contiguous cells but he specified them in an odd way.

 

[Norie]

Ark68

What ranges do you want to merge?

Do you actually need to merge?

Wouldn't Center across Selection would work?

 

[Ark68]

I think I may have been misleading.

I'd like to merge cells {AC24, AD24, AE24}, {AB25, AC25, AD25}, {AC26, AD26, AE26}, {AB27, AC27, AD28}.

I have used this similar routine for many other cell ranges with no problems. This is the only scenario it's not.
eg, this similar code works in another module:

        With Range("AI24:AL24, AJ25:AL25, AI26:AK26")
            .MergeCells = True
            .Locked = False
        End With

I use these individual merged cell ranges as fields for drop down list, I'd prefer to merge.

 

[Norie]

This works just fine for me.

      With Range("AC24:AE24, AB25:AD25,AC26:AE26,AB27:AD28")
            .MergeCells = True
            .Locked = False
        End With

 

[Rick Rothstein]

I think I may have been misleading.

I'd like to merge cells {AC24, AD24, AE24}, {AB25, AC25, AD25}, {AC26, AD26, AE26}, {AB27, AC27, AD28}.

I have used this similar routine for many other cell ranges with no problems. This is the only scenario it's not.
eg, this similar code works in another module:

        With Range("AI24:AL24, AJ25:AL25, AI26:AK26")
            .MergeCells = True
            .Locked = False
        End With

I use these individual merged cell ranges as fields for drop down list, I'd prefer to merge.

Try it like this...

Sub MergeMyCells()
  Dim Cell As Range
  For Each Cell In Range("AC24,AB25,AC26,AB27")
    With Cell.Resize(, 3)
      .MergeCells = True
      .Locked = False
    End With
  Next
End Sub

Note that single cells do not have to be specified using "Address:Address" format.

 

[Ark68]

Thanks folks ...

Norie, as I said, I have no idea why it didn't work in my situation as similar have worked consistently elsewhere. Needless to say, Rick, your suggestion solved the problem. Thanks!