excel question VBA to create table

Hi,

I need help with macro that when i run, it selects the current region and then converts it to table.

i used macro recorder which gave me the following code. but as you can see that the range Range("$A$1:$AM$13") is hardcorded and it is not dynamic next time, if i want to use this macro, it will only create table from range Range("$A$1:$AM$13"),  which is incorrect, becuase my data range varies.
any help appreciated.

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.CurrentRegion.Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AM$13"), , xlYes).Name = _
        "Table1"
    ActiveCell.Range("Table1[#All]").Select
End Sub

Open in new window

LVL 6
FloraAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Replace

Range("$A$1:$AM$13")

with

Selection.currentregion
0
 
FloraAuthor Commented:
thanks Saqib,

and how to change it if i want to use the UsedRange instead of CurrenRegion?
0
 
Saqib Husain, SyedEngineerCommented:
Activesheet.usedrange
0
 
Rob HensonFinance AnalystCommented:
Slight flaw in logic, I think.

If the UsedRange is not the same as CurrentRegion then that implies there are blank columns or rows on the sheet after which there is more data. Blank rows in a table don't make sense but won't do any harm but it wouldn't like blank columns. They would end up with a generic "Column1" header which you might not want.

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.