21

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Janu
  • 215
  • 1
  • 2
  • 5
  • 2
    Will it always start in A1? – Ben Black Feb 04 '14 at 16:33
  • You should see this, it will help you understand some important concepts: **[Excel macro - Avoiding using Select](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select)**. Sure in your particular case you start from a selection but then you should put it in a variable. – simpLE MAn Feb 04 '14 at 16:37
  • 2
    Like @pnuts said this is already part of macro, they even include the HotKey of *Ctrl* + *L*, you don't even need to select the entire range just ANY cell in the Range you want to tbe a table. – user2140261 Feb 04 '14 at 16:43
  • Thank you for the suggestion (I was unaware of the shortcut) however, there are some blank cells within the sheet and Ctrl + L ends at those cells even though the data goes beyond them. – Janu Feb 07 '14 at 18:51

1 Answers1

40

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
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80