VBA to Format Current Selection as a Table

[nirvehex]

Hi,

I'm trying to format the current selection as Table2.

My code:

Range("A1").Select 
Range(Selection, Selection.End(xlToRight)).Select 
Range(Selection, Selection.End(xlDown)).Select 
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$CP$52"), , xlYes).Name = _ 
"Table2"

How do I get rid of that A1:CP52 range and just do current selection?

Thanks.

 

[NeonRedSharpie]

Range("A1").SelectRange(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
"Table2"

 

[Norie]

Try this.

Set rngData = Range(Range("A1"), Range("A1").End(xlToRight).End(xlDown))

ActiveSheet.ListObjects.Add(xlSrcRange, rngData, , xlYes).Name = _ 
"Table2"

 

[iliauk]

Try this

Dim rng As Range
Dim loc As String
loc = Selection.Address

ActiveSheet.ListObjects.Add(xlSrcRange, Range(loc), , xlYes).Name = _
"Table2"

 

[nirvehex]

Perfect. Thanks Norie!