VBA to convert a table to a range?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA to convert a table to a range?

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to convert a table to a range?

     
    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?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,987
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert a table to a range?

    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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    New Member
    Join Date
    Oct 2013
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Re: VBA to convert a table to a range?

    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.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,987
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert a table to a range?

    Hi

    I'm glad it helped.

    Quote Originally Posted by gisquared View Post
    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 by pgc01; Aug 8th, 2017 at 05:36 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to convert a table to a range?

      
    Quote Originally Posted by pgc01 View Post
    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/0B5Y...ew?usp=sharing
    Thank you so much

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com