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:
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.