Need help improving my code (dynamic range and format as table)

[koskesh]

I used the macro recorder to format a table.

        Sheets("M1").Activate
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp

    'this part should be dynamic - for rows and columns
    Range("A1:D5224").Select
    Range("B8").Activate
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$5224"), , xlYes).Name = _
        "Tabelle3"
    Range("Tabelle3[#All]").Select

The first part is allways the same (The SAP Output is weird, so I need to delete the 3 rows and first column)
But the last part A1:D5224 differs every time. I need a dynamic code to find the last cell and column and format a table. Thank you!

 

[Gilliam]

i believe this should help.

Sub koskesh()
    Sheets("M1").Activate
    Range("A1", "A2").EntireRow.Delete
    Range("A1").EntireColumn.Delete
    
    Range("A1", Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", Cells(Range("A1").End(xlDown).Row, _
        Range("A1").End(xlToRight).Column)), , xlYes).Name = "Tabelle3"
End Sub

cheers,
Matt

 

[koskesh]

Hi Matt, awesome! Thank you. Can you help me to repeat the macro for two worksheets. M1 and M2 (rename into Table1, Table2)

 

[Gilliam]

is this going to be the same macro (i.e. within the same sub?), or a different one?

if its the same macro, then this ought to do it.

Sub koskesh()
    For x = 1 To 2
        Sheets("M" & x).Activate
        Range("A1", "A2").EntireRow.Delete
        Range("A1").EntireColumn.Delete
        
        Range("A1", Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)).Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", Cells(Range("A1").End(xlDown).Row, _
            Range("A1").End(xlToRight).Column)), , xlYes).Name = "Table" & x
    Next
End Sub

 

[koskesh]

Its the same macro. I found an issue with the code. I have columns where the first row is empty but not the rest of the column. The code does not work in that situation and sometimes it formats all columns as a table. I dont know why.
Thank you

 

[Gilliam]

Its the same macro. I found an issue with the code. I have columns where the first row is empty but not the rest of the column. The code does not work in that situation and sometimes it formats all columns as a table. I dont know why.
Thank you

yes, that was my bad, it wasn't very robust. when data was 2 columns wide
OOPS
Here ya go.

Sub koskesh()
    For x = 1 To 2
        'selects appropriate sheet
        Sheets("M" & x).Activate
        Range("A1", "A2").EntireRow.Delete
        Range("A1").EntireColumn.Delete
        'selects the used range after deletion and inserts table with appropiate name
        Range("A1", Cells(Cells(Rows.Count, 1).End(xlUp).Row, Range("AA1").End(xlToLeft).Column)).Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", Cells(Cells(Rows.Count, 1).End(xlUp).Row, _
            Range("AA1").End(xlToLeft).Column)), , xlYes).Name = "Table" & x
    Next
End Sub

 

[koskesh]

thank you!