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

koskesh

New Member
Joined
May 30, 2014
Messages
41
I used the macro recorder to format a table.

Code:
        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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
i believe this should help.
Code:
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
 
Upvote 0
Hi Matt, awesome! Thank you. Can you help me to repeat the macro for two worksheets. M1 and M2 (rename into Table1, Table2)
 
Upvote 0
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.
Code:
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
 
Last edited:
Upvote 0
ADVERTISEMENT
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
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,196,413
Messages
6,015,119
Members
441,872
Latest member
Jyyyyyyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top