How to dynamically reference the very last cell in any given table?

Question

I'm trying to make a button that resets a given table to it's.. let's say default size and formating

I've spent some to get Range.Resize property right, but later found out that after resizing or scaling down the table it requires me to clean up everything that's left behind.

...
Sub Bt_clear_tb1()
    'ScreenUpdate
    Application.ScreenUpdating = False
    Application.CutCopyMode = False

    'Resize and cleanup
    With ActiveSheet.ListObjects("Tab1")
        .Resize(.Range.Resize(10))
        .ListColumns(1).DataBodyRange.ClearContents
        .ListColumns(2).DataBodyRange = _ ...
        .ListColumns(3).DataBodyRange = _ ...
    End With

    'Clear the rest | ---► Don't want it to be fixed ◄--- |
        Range("A11", "Q11").Select                   ◄--- |
        Range(Selection, Selection.End(xlDown)).Select ◄--|
        Selection.ClearContents                      ◄--- |

    'ScreenUpdate
     Application.ScreenUpdating = True
End Sub
...

Of course I got it to work manually on a fixed ranges, but I can't find a way to get the target range dynamically. So if I ever decided to move or resize it differently I wouldn't have to keep rewriting the code every time.

Almost thought I got it

like:

wb.ListObjects(1).Range.Cells(.Range.Rows.Count, .Range.Columns.Count).Offset(1,0).Select

Range(Selection, Selection.End(xlToLeft).End(xlDown).Select
Selection.ClearContents

But it's not dynamic.. if I change ListObjects(1) to other table it still clears area below the first one.

Do you know any other way to somehow get the 'Range of an area below the table'?

~also please, excuse my terrible English.. I hope this is readable and somehow makes sense

Not your issue, but you may want to take a look at [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Feb 06 '19 at 17:06

score 1 · Answer 1 · answered Feb 06 '19 at 17:01

1

Like so:

Sub Bt_clear_tb1()
    Dim lSize As Long
    Const ROWS2KEEP As Long = 10
    'ScreenUpdate
    Application.ScreenUpdating = False
    Application.CutCopyMode = False

    'Resize and cleanup
    With ActiveSheet.ListObjects("Table1")
        lSize = .ListRows.Count
        .Resize (.Range.Resize(ROWS2KEEP))
        .ListColumns(1).DataBodyRange.ClearContents
        .ListColumns(2).DataBodyRange = "_ ..."
        .ListColumns(3).DataBodyRange = "_ ..."
        .Range.Offset(ROWS2KEEP).Resize(lSize - (ROWS2KEEP - 1)).ClearContents
    End With
    'ScreenUpdate
     Application.ScreenUpdating = True
End Sub

answered Feb 06 '19 at 17:01

jkpieterse

2,727
1
9
18

.Range.Offset(ROWS2KEEP).Resize(lSize - (ROWS2KEEP - 1)).ClearContents throws Run-time error '1004': Application-defined or object-defined error – Vickyyy Feb 06 '19 at 17:14
It ran fine for me, but perhaps your situation i slightly different from my testing setup :-) – jkpieterse Feb 07 '19 at 09:50

score 0 · Accepted Answer · answered Feb 06 '19 at 18:03

Thanks for the hint!

I found out the following solves my problem:

    Sub bt_table_1_clean()
    'ScreenUpdate
    Application.ScreenUpdating = False
    Application.CutCopyMode = False

    '@@@ Resize and cleanup
     Const ROWS2KEEP As Long = 20

    With ActiveSheet.ListObjects("Tab1")
        .Resize (.Range.Resize(ROWS2KEEP + 1))
        .ListColumns(1).DataBodyRange.ClearContents
        .ListColumns(2).DataBodyRange = ...
        .ListColumns(3).DataBodyRange = ...
        .Range.Offset(ROWS2KEEP + 1).Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents
    End With        

    'ScreenUpdate
    Application.ScreenUpdating = True
End Sub

This is what I needed:

.Range.Offset(ROWS2KEEP + 1).Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents

INstead of adding 1 to ROWS2KEEP two times you could have defined ROWS2KEEP as 21 and omit the two additions :-) — jkpieterse, Feb 07 '19 at 09:52

How to dynamically reference the very last cell in any given table?

2 Answers2