Format range as table

Question

I have a set of data that changes in length. I would like to make a macro that does various things, including making the entire data set a table?

Using Selection.End(xlToRight) and xlToDown to select the data works, but when the recorded macro converts it to a table it does it only to the selected range.

Here is what the record function gives me:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$26095"), , xlYes).Name _
    = "Table1"
Range("Table1[#All]").Select

Is there a way to make the range dynamic based on the range selection above?

So variables are way beyond my ability. How would I use Selection? — OFFICE_HELP_
You would change Range("$A$1:$H$26095") to Selection but highly recommend you read the linked thread regardless. — BigBen

VBasic2008 VBasic2008 · Accepted Answer · 2021-06-11T00:05:44

Range to Table

Various errors will occur if you already have added the table, or the table overlaps with another, etc. So I added a procedure that deletes all tables in the worksheet Sheet1 in the workbook containing this code.

Option Explicit

Sub RangeToTable()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim tbl As ListObject
    Set tbl = ws.ListObjects.Add(xlSrcRange, rg, , xlYes)
    tbl.Name = "Table1"
End Sub

Sub DeleteAllTables()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim tbl As ListObject
    For Each tbl In ws.ListObjects
        tbl.Delete
    Next tbl
End Sub

If you want to kind of 'stick to your guns' (not recommended), you could use the following one-liner:

ActiveSheet.ListObjects.Add(xlSrcRange, _
    Range("A1").CurrentRegion, , xlYes).Name = "Table1"

There is no need to select anything.

Format range as table

1 Answers

Range to Table