Use VBA to Create Table with Unkown Range

[ABlohm]

I would like to create a table with an unknown range. I currently have the following.

Sub Button2_Click()

Dim r As Range

Set r = Worksheets("Sheet2").Range("A1").CurrentRegion
Worksheets("Sheet2").ListObjects.Add(xlSrcRange, Range("A1:B4"), xlYes).Name = "MyTable"

End Sub

An example of the Excel data on Sheet 2.

Job Position Time
V1 10
V2 11
V3 12

What I have above does not work. It will work if Range() has Range("A1:B4"). I thought that if I defined r as a range, it will substitute "A1:B4" in Range().

I receive a Run Time Error 1004 and Method Range of Object Global Failed.

 

[Domenic]

You'll need to replace Range("A1:B4") with r. Also, since you're not using named arguments, you'll need to place an additional comma for the third missing argument. Therefore, try...

[COLOR=darkblue]Sub[/COLOR] Button2_Click()

    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]Set[/COLOR] r = Worksheets("Sheet2").Range("A1").CurrentRegion
    
    Worksheets("Sheet2").ListObjects.Add(xlSrcRange, r, , xlYes).Name = "MyTable"

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To use named arguments...

[COLOR=darkblue]Sub[/COLOR] Button2_Click()

    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]Set[/COLOR] r = Worksheets("Sheet2").Range("A1").CurrentRegion
    
    Worksheets("Sheet2").ListObjects.Add(SourceType:=xlSrcRange, Source:=r, XlListObjectHasHeaders:=xlYes).Name = "MyTable"

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[ABlohm]

It works, thanks for the explanation!