Merged cells - identifying and then unmerge

[TheWennerWoman]

I have a huge worksheet (it runs from column A to ZY).

About forty of the columns are merged. To unmerge them (when I need to apply some formatting where the cells have to be unmerged) I am using this bit of code with an entry for each column

Sub Unmerge()

    Columns("E:E").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Unmerge
        Columns("AD:AD").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Unmerge

And so on for each merged column.

The problem I have is if I insert a column anywhere in the sheet, anything merged to the right of where I have inserted moves along by 1.

So if column E is merged and I insert a new column at C, column E isn't merged anymore, column F is and so I have to go through my VBA and modify accordingly for all affected columns.

I have got this which identifies if a column is merged

Sub check_if_merged()

For b = 5 To 10

If Sheet16.Cells(1, b).MergeCells Then
  MsgBox "Merged!"
  Else
  MsgBox "Not Merged!"
End If

Next b
End Sub

Is there a way I can adapt this to identify the columns that are merged, put them into an array and then substitute

Columns("E:E").Select

etc with the stored values?

Thanks for reading.

 

[HighAndWilder]

I have a huge worksheet (it runs from column A to ZY).

About forty of the columns are merged. To unmerge them (when I need to apply some formatting where the cells have to be unmerged) I am using this bit of code with an entry for each column

Sub Unmerge()

    Columns("E:E").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Unmerge
        Columns("AD:AD").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Unmerge

And so on for each merged column.

The problem I have is if I insert a column anywhere in the sheet, anything merged to the right of where I have inserted moves along by 1.

So if column E is merged and I insert a new column at C, column E isn't merged anymore, column F is and so I have to go through my VBA and modify accordingly for all affected columns.

I have got this which identifies if a column is merged

Sub check_if_merged()

For b = 5 To 10

If Sheet16.Cells(1, b).MergeCells Then
  MsgBox "Merged!"
  Else
  MsgBox "Not Merged!"
End If

Next b
End Sub

Is there a way I can adapt this to identify the columns that are merged, put them into an array and then substitute

Columns("E:E").Select

etc with the stored values?

Thanks for reading.

You can create a range reference for all of the merged columns and then apply formatting to the range.

You don't actually have to select the range to apply the formatting.

Sub check_if_merged()
Dim b As Integer
Dim rng As Range

    For b = 5 To 10
      
        If Sheet1.Cells(1, b).MergeCells Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, Columns(b))
            Else
                Set rng = Columns(b)
            End If
        End If
    
    Next b
    
    rng.Select

End Sub

 

[TheWennerWoman]

Many thanks!