.unlist method will not work

2019-09-14 23:34发布

This is probably a simple question, but I cannot find the answer anywhere on the net. I am trying to unlist all the tables on a worksheet. This macro fails with a message that says "Compile Error: Method or Data Member not found" as it highlights the .Unlist part of the macro. I have tried other variations but .Unlist does not seem to want to work. I am on Excel for Mac 2011 (version 14.4)

Sub UnlistAllTablesOnSheet()
    Sheets("Role 1").Select
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
         oSh.ListObjects(oLo.Name).Unlist
         MsgBox oLo.Name & "now unlisted"
    Next
End Sub

1条回答
神经病院院长
2楼-- · 2019-09-15 00:13

It seems that the Method to convert a ListObject to a Range is called ConvertToRange in Excel 2011.

Note that there are other issues in your code

  1. use of Select and ActiveSheet is not required
  2. nor is selecting the list object (GoTo ...)
  3. once a listObject has been UnListed, the variable oLo will no longer be set, so MsgBox oLo.Name ... will error

To make the code work on either PC or Mac, use Conditional Compilation

Sub UnlistAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim nm As String
    Set oSh = Sheets("Role 1")
    For Each oLo In oSh.ListObjects
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
        nm = oLo.Name
        #If Mac Then
            oLo.ConvertToRange
        #Else
            oLo.Unlist
        #End If
        MsgBox nm & "now unlisted"
    Next
End Sub
查看更多
登录 后发表回答