CODEWITHSUNDEEP
excelvba
user394334
user394334

Reputation: 277

Create a table with a macro

I have this simple range:

enter image description here

I want to create a macro which creates a table from a range. The macro I get is this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$B$3"), , xlYes).Name = _
        "Table12"
    ActiveCell.Range("Table12[#All]").Select
End Sub

The problem is that I want the macro to not be contingent on a specific size. I for instance want it to be able to work with this table as range aswell:

enter image description here

The problem in the code seems that it uses "$A$1:$B$3" but it should be independent of that. Is there a simple way to fix this? How can I record the macro so that it works for all tables?

Upvotes: 0

Views: 1508

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

The next piece of code will create a table ("MyTable") starting from the active cell to the adjacent most right column and to the down filled row of active cell column:

Sub TableRightDownOfSelection()
    Dim rnG As Range
    UnlistIt

    Set rnG = Range(ActiveCell.Address & ":" & Cells(Cells(Rows.count, _
         ActiveCell.Column).End(xlUp).Row, ActiveCell.End(xlToRight).Column).Address)
    ActiveSheet.ListObjects.Add(xlSrcRange, rnG, , xlYes).Name = "MyTable"
End Sub

Sub UnlistIt()
    On Error Resume Next
    ActiveSheet.ListObjects("Table1").Unlist
    If Err.Number > 0 Then Err.Clear
    On Error GoTo 0
End Sub

In order to avoid an error message, in case you try the code again, the UnlistIt Sub will be called before creating the table...

Upvotes: 2

Storax
Storax

Reputation: 12207

You could use the currentregion property 

Sub CreateTbl()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim rg As Range
    Set rg = ws.Range("A1").CurrentRegion

    ws.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "myTable"

End Sub

But be aware this code will create a table named like myTable_1 in case you already have a table namend myTable somewhere else and it will faill if you run it twice for the same range.

Addition: Based on the comments one could try to use the active cell

Sub CreateTbl()

    Dim ws As Worksheet
    Dim rg As Range
    Set rg = ActiveCell.CurrentRegion

    Set ws = rg.Parent
    ws.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "myTable"

End Sub

Upvotes: 2

Related Questions