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
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
Select
andActiveSheet
is not requiredGoTo ...
)UnList
ed, the variableoLo
will no longer be set, soMsgBox oLo.Name ...
will errorTo make the code work on either PC or Mac, use Conditional Compilation