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

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
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 :

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

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

But I just get the same error? :banghead:

Any suggestions?
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the sheet protected?
 

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,845
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,635
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top