CODEWITHSUNDEEP
excelvba
Vickyyy
Vickyyy

Reputation: 1

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

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

Upvotes: 0

Views: 148

Answers (2)

Vickyyy
Vickyyy

Reputation: 1

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

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 2956

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

Upvotes: 1

Related Questions