Home > Blockchain >  ListObject Error upon applying an Unlist Method
ListObject Error upon applying an Unlist Method

Time:04-21

Basically, I have an Excel Formatted Table called "TestTable" in my activesheet. That's the only table in that sheet. I'm trying to convert it to a normal range. From looking up online, this should be simple, all I have to do is Unlist that table object. However, my VBA code is throwing an error. Any pointers in the right direction would be greatly appreciated.

Sub ConverToNormalRange()
Dim objListObj As ListObject
Set objListObj = ActiveSheet.ListObjects(1)
objListObj.Unlist
End Sub

When I run the above macro, I get the following error:

Error upon calling Unlist method on ListObject

CodePudding user response:

Convert First Table to a Range

Sub ConvertToRange()
    Const ProcName As String = "ConvertToRange"
    On Error GoTo ClearError
    
    With ActiveSheet ' improve!
        If .ListObjects.Count > 0 Then
            Dim tblName As String
            With .ListObjects(1)
                tblName = .Name
                .Unlist
            End With
            MsgBox "Table '" & tblName & "' converted to a range.", _
                vbInformation
        Else
            MsgBox "No table found in worksheet '" & .Name & "'.", _
                vbExclamation
        End If
    End With

ProcExit:
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub

CodePudding user response:

I tried converting the table manually and it wasn't doing anything either. So then I figured it wasn't a VBA problem. It turns out that I had connections open in Power Query, and it was preventing the table from converting back to normal range.

Open power query connections don't allow you to convert table to normal range

  • Related