VBA to convert a table to a range?

[krice1974]

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?

 

[pgc01]

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

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

 

[gisquared]

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.

 

[pgc01]

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:

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.

 

[Paulart69]

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:

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

 

[Jeeremy7]

Hello,

This post is 11 years old and still working thanks a lot !

 

[oggiez]

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.

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

 

[Beepee]

Hi. Now 2023 and this post is still helping. Thanks for keeping these things available.
B

 

[Shiseiji]

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