VBA to convert a table to a range?

krice1974

Active Member
Joined
Jul 3, 2008
Messages
422
Hello all. I see that I can easily record the code to convert a range to a table, but nothing is recorded when converting a table to a range. Any suggestions?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi

A Table in vba is a ListObject. You can just UniList it.

Notice, however, that you may also want to remove formattting. In that case store the ListObject Range before unlisting it. Ex.:

Code:
Sub ConvertTableToRange()
Dim rList As Range
 
With Worksheets("Sheet3").ListObjects("Table1")
    Set rList = .Range
    .Unlist                           ' convert the table back to a range
End With

With rList
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With
End Sub
 
Upvote 0
Thank you pgc01,
This post is 8 years old... but it still works. I just had this problem and this code worked great.

I modified it just a little because the workbook I was using had 2 worksheets where each one was a table.

If you have multiple worksheets with tables, ensure you know what each table is named.

Example.

Using pgc01's code:
Dim rList As Range

With Worksheets("Sheet1").ListObjects("Table1")
Set rList = .Range
.Unlist ' convert the table back to a range
End With

Dim rList1 As Range

With Worksheets("Sheet2").ListObjects("Table2")
Set rList1 = .Range
.Unlist ' convert the table back to a range
End With



pgc01 you are a life saver.
 
Upvote 0
Hi

I'm glad it helped.

If you have multiple worksheets with tables, ensure you know what each table is named.

Just to make it clearer, if you just have 1 table per worksheet you don't need to know its name, you can use the index, like:

Code:
Sub ConvertTableToRange()
Dim rList As Range
Dim ws As Worksheet
 
For Each ws In Worksheets(Array("Sheet2", "Sheet3"))
    With ws.ListObjects(1)
        Set rList = .Range
        .Unlist                           ' convert the table back to a range
    End With
    
    With rList
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = xlColorIndexAutomatic
        .Borders.LineStyle = xlLineStyleNone
    End With
Next ws
End Sub

... and if you want to get rid of all the tables in the workbook you can loop through all the tables in all the worksheets.
 
Last edited:
Upvote 0
Hi

I'm glad it helped.



Just to make it clearer, if you just have 1 table per worksheet you don't need to know its name, you can use the index, like:

Code:
Sub ConvertTableToRange()
Dim rList As Range
Dim ws As Worksheet
 
For Each ws In Worksheets(Array("Sheet2", "Sheet3"))
    With ws.ListObjects(1)
        Set rList = .Range
        .Unlist                           ' convert the table back to a range
    End With
    
    With rList
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = xlColorIndexAutomatic
        .Borders.LineStyle = xlLineStyleNone
    End With
Next ws
End Sub

... and if you want to get rid of all the tables in the workbook you can loop through all the tables in all the worksheets.




Hello,

I need your help or someone from the community. I am trying since 4 hours to make it work in my case, almost the same. Do you have an idea of what is wrong ?
Please find my file in my drive https://drive.google.com/file/d/0B5YQXffHf4okZl9rSExGaFFvQmk3RGh4SUhkT19XRTBSTUNR/view?usp=sharing
Thank you so much
 
Upvote 0
Just to go a little further with this topic. To convert all tables in all worksheets use the following code. Note it does not matter how many sheets or tables you have in the workbook.

VBA Code:
Sub ConvertAllTablesToRange()
Dim rList As Range
Dim ws As Worksheet
Dim i As Integer

For Each ws In Worksheets
    i = ws.ListObjects.Count                  ' gets the starting number of tables on the sheet
   
    Do While i > 0                            ' loops through every table on the sheet before going to the next sheet
        With ws.ListObjects(1)
            Set rList = .Range
            .Unlist                           ' convert the table back to a range
        End With
       
        With rList
            .Interior.ColorIndex = xlColorIndexNone
            .Font.ColorIndex = xlColorIndexAutomatic
            .Borders.LineStyle = xlLineStyleNone
        End With
        i = ws.ListObjects.Count              ' updates number of tables left on the sheet.
    Loop
Next ws
End Sub
 
Upvote 0
Hi. Now 2023 and this post is still helping. Thanks for keeping these things available.
B
 
Upvote 0
Ditto "this post is still helping. Thanks for keeping these things available."
Soon to retire, but Mr. Excel and the incredibly kind and helpful members have helped me more than I could ever repay since ~ 1997. Later I had to make a new profile.

Ron
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top