Creating list removes cell merges

[paul.domaskis]

Excel 2003...there doesn't seem to be any warning that cell merges are
lost when turning ranges into lists. Is this the defactor norm, or is
there a way to turn on such warnings

 

[Dave Peterson]

I don't see a way to get the warning using the menus.

You could convert a range into a list using a macro and then you could inspect
that range for merged cells first.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim resp As Long
Dim myListObj As ListObject
Dim Rng As Range

Set wks = ActiveSheet
Set Rng = Selection

With wks
If Rng.MergeCells = True _
Or IsNull(Rng.MergeCells) Then
resp = MsgBox(prompt:="Selection has merged cells. Continue?", _
Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
Else
Rng.UnMerge
End If
End If
Set myListObj = .ListObjects.Add _
(SourceType:=xlSrcRange, _
Source:=Rng)
myListObj.Name = "List_" & Rng.Address(0, 0)

End With

End Sub


Or just pop up that dialog after a warning message:

Option Explicit
Sub testme2()

Dim wks As Worksheet
Dim resp As Long
Dim Rng As Range

Set wks = ActiveSheet
Set Rng = Selection

With wks
If Rng.MergeCells = True _
Or IsNull(Rng.MergeCells) Then
resp = MsgBox(prompt:="Selection has merged cells. Continue?", _
Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If
End If

Application.Dialogs(xlDialogCreateList).Show
End With

End Sub

 

[paul.domaskis]

Thanks, Dave.

I don't see a way to get the warning using the menus.

You could convert a range into a list using a macro and then you could inspect
that range for merged cells first.

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim resp As Long
    Dim myListObj As ListObject
    Dim Rng As Range

    Set wks = ActiveSheet
    Set Rng = Selection

    With wks
        If Rng.MergeCells = True _
         Or IsNull(Rng.MergeCells) Then
            resp = MsgBox(prompt:="Selection has merged cells.  Continue?", _
                Buttons:=vbYesNo)
            If resp = vbNo Then
                Exit Sub
            Else
                Rng.UnMerge
            End If
        End If
        Set myListObj = .ListObjects.Add _
                             (SourceType:=xlSrcRange, _
                             Source:=Rng)
        myListObj.Name = "List_" & Rng.Address(0, 0)

    End With

End Sub

Or just pop up that dialog after a warning message:

Option Explicit
Sub testme2()

    Dim wks As Worksheet
    Dim resp As Long
    Dim Rng As Range

    Set wks = ActiveSheet
    Set Rng = Selection

    With wks
        If Rng.MergeCells = True _
         Or IsNull(Rng.MergeCells) Then
            resp = MsgBox(prompt:="Selection has merged cells.  Continue?", _
                Buttons:=vbYesNo)
            If resp = vbNo Then
                Exit Sub
            End If
        End If

        Application.Dialogs(xlDialogCreateList).Show
    End With

End Sub