Macro for Creating Tables in Excel 2007

[MESTRELLA29]

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

 

[Joel]

Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with

 

[David]

Hi,

Another way to set the range is:

Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

Then used the variable SelectionAddress to set the address.

David

 

[Joel]

David: If there are blank cells in column A your method may not work.
curtrent area stops when it finds a blank cell in the first row or fist
column.

 

[Ron de Bruin]

I like this instead of .ListObjects.Add code line
No problems if the Table already exist

Application.CommandBars.ExecuteMso "TableInsertExcel"


I use it it this Table Tools add-in
http://www.rondebruin.nl/table.htm

 

[David]

Hi,
Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
range("A1").select
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

As long as you are somewhere in the table, this will work.
range("A1").select will make sure you are in the table. Blanks will not
matter,

David

 

[MESTRELLA29]

I tied what you recomended but it only turn the first column into table, the
rest remain the same...

 

[Ron de Bruin]

Excel guess what the range is if you not select it first
If you have a empty column or row in the range it stop

 

[MESTRELLA29]

Just wanted to let you know what finally work for me, Thanks for your help.

Range("A1").Select
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address

Application.CommandBars.ExecuteMso "TableInsertExcel"