Format data as table theme using only the used range

Hi KarenYK

Welcome to the Forum!

Try this

Option Explicit

Sub Test()
    Dim LR As Long, LC As Long
    Dim ws As Worksheet

    Set ws = Sheets("Before Table Format")
    With ws
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column

        With ws.ListObjects
            .Add(xlSrcRange, Range(Cells(9, 1), Cells(LR, LC)), , xlYes).Name = "Table1"
            ws.ListObjects("Table1").TableStyle = "TableStyleMedium2"
        End With
    End With
End Sub