VBA to flatten tables (ListObjects) to ranges (XL2013)

[AOB]

Hi everybody,

I need some code to convert all tables in a workbook to "flat" ranges (that is, retain the data, just get rid of the table structure around it)

I thought this would be fairly straightforward :

Public Sub FlattenTables()
    Dim sht As Worksheet
    Dim lso As ListObject
    For Each sht In ThisWorkbook.Sheets
        For Each lso In sht.ListObjects
            lso.Unlist
        Next lso
    Next sht
End Sub

But for some reason it throws a run-time error (1004) every time it tries to unlist the object?

Application-defined or object-defined error

My first instinct was that I couldn't unlist a listobject while it is held as a ListObject variable (i.e. by doing so, it is no longer a ListObject variable, thus conflict)

So then I tried just compiling a list of table names and then unlisting them individually outside the object scope like so :

ThisWorkbook.Sheets("SheetName").ListObjects("TableName").Unlist

But I just get the same error?

Any suggestions?

 

[RoryA]

Is the sheet protected?

 

[AOB]

Nope

 

[pgc01]

Does it work directly in the worksheet interface?

 

[AOB]

Weirdly, no - I thought it did? I'm prompted as normal :

Do you want to convert the table to a normal range?

But after clicking Yes, the table remains (and is still present in the Name Manager)

 

[pgc01]

Hi

I don't know what's happening, but the next things I'd do would be to

- check if it's some add-in or customization that's interfering with the conversion (execute excel in safe mode)

- see if it's some corruption in the file (try open and repair that file)


It can also happen that your file is corrupted beyond repair. In that case salvage what you can


... also wait some more time for cleverer suggestions.