.unlist方法不起作用 [英] .unlist method will not work

问题描述

这可能是一个简单的问题，但是我无法在网络上的任何地方找到答案.我试图取消列出工作表上的所有表.该宏失败，并显示一条消息编译错误:未找到方法或数据成员"，因为它突出显示了宏的.Unlist部分.我尝试了其他变体，但是.Unlist似乎并不想工作.我在Mac 2011(版本14.4)上使用Excel

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

推荐答案

在Excel 2011中，似乎将ListObject转换为Range的方法称为ConvertToRange.

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. 不需要使用Select和ActiveSheet
2. 也没有选择列表对象(GoTo ...)
3. 一旦UnList已添加listObject，将不再设置变量oLo，因此MsgBox oLo.Name ...将出错

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

要使代码在PC或Mac上均可使用，请使用条件编译

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

这篇关于.unlist方法不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！