Results 1 to 2 of 2

VBA to convert a table to a range?

This is a discussion on VBA to convert a table to a range? within the Excel Questions forums, part of the Question Forums category; Hello all. I see that I can easily record the code to convert a range to a table, but nothing ...

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Location
    New Jersey
    Posts
    422

    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
    15,714

    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.

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