Format data as table theme using only the used range

What I am trying to accomplish is having some data that looks just plain (tab titled "Before Table Format") end up to look like what is on tab "When Done".
I'm having issues getting the range selected to be dynamic as this data is never the same number of rows.


Thanks!

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

You're welcome...glad I could help. Thanks for the Rep.