Make Table using .UsedRange in VB instead of using cntl-T

[GeeWhiz7]

Hi there,
I have a worksheet whose data change periodically leading to larger or smaller ranges of columns being used depending on the data. The number of rows are always the same.

In my VBA module that gets the data, I would like to make a table (instead of cntl-T afterwards) with the data each time I grab it and always have the same table name.

I've tried a few things including this below which will only make a table of the A column.

I tried using ActiveSheet.UsedRange.Select assigned to a Range variable, but that didn't work.

Since my data table changes size I can't use a pre-defined range in the column direction so I'm not sure how to make it expand or shrink to "wrap" columns for making it into a table. I tried counting columns, converting the max to a letter and then putting it in, but Is there a way to do this without having to count columns and pass that to the second part of the range below?

Thank you!

Worksheets("BaseData").Activate
  
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:A" & 1259), , xlYes).Name = _
        "BaseDataTable"

    Range("Table1[#All]").Select

 

[Trebor76]

Hi GeeWhiz7,

Try this:

Option Explicit
Sub Macro1()

    Dim lngLastCol As Long
    
    Application.ScreenUpdating = False
    
    With Worksheets("BaseData")
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .ListObjects.Add(xlSrcRange, Range(.Cells(1, 1), Cells(1259, lngLastCol)), , xlYes).Name = "BaseDataTable"
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[NoSparks]

another option to try

With Worksheets("BaseData")
    .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "BaseDataTable"
End With

 

[GeeWhiz7]

Hi GeeWhiz7,

Try this:

Option Explicit
Sub Macro1()

    Dim lngLastCol As Long
   
    Application.ScreenUpdating = False
   
    With Worksheets("BaseData")
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .ListObjects.Add(xlSrcRange, Range(.Cells(1, 1), Cells(1259, lngLastCol)), , xlYes).Name = "BaseDataTable"
    End With
   
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

Hi Robert,

Thanks for the solution suggestion. I gave it a try and get this error and the highlight in VBA shows on the line starting with .ListObjects...
Thanks, -Will

    With Worksheets("BaseData")
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .ListObjects.Add(xlSrcRange, Range(.Cells(1, 1), Cells(1259, lngLastCol)), , xlYes).Name = "BaseDataTable"
    End With

 

[GeeWhiz7]

Hi Robert,

Thanks for the solution suggestion. I gave it a try and get this error and the highlight in VBA shows on the line starting with .ListObjects...
Thanks, -Will
View attachment 52804

    With Worksheets("BaseData")
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .ListObjects.Add(xlSrcRange, Range(.Cells(1, 1), Cells(1259, lngLastCol)), , xlYes).Name = "BaseDataTable"
    End With

Thanks Robert and NoSparks! It works now. The issue was the second "Cell" was missing a "." in front of it. Changing that made it work.