Excel Macro - Select all cells with data and format as table

excel, vba

Solution

Try this one for current selection:

Sub A_SelectAllMakeTable2()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
End Sub

or equivalent of your macro (for Ctrl+Shift+End range selection):

Sub A_SelectAllMakeTable()
    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
End Sub

Problem

Is it possible to write a macro that can format a table out of any active selection? For instance, I have a macro that will basically just do a Ctrl+Shift+End range selection. After that, I would like the macro to be able to format the selected range as a table, however when I record this action in VBA, it will use the range addresses, which will not always be the same from sheet to sheet. ``` Sub A_SelectAllMakeTable() Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AO$2959"), , xlYes).Name _ = "Table1" Range("A1:AO2959").Select ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium15" End Sub ``` Thanks in advance.

Original source

Related problems