Reference a table by index number

Question

I'm trying to clear cell contents within a specified table column range ([Front Straddle]:[Front Option]), of all tables within a specified worksheet. This script will only live within the "VolJump" worksheet, which contains an arbitrary number of identically formatted, differently named tables. Because of this, I felt the best approach was to reference the tables by the index number.

I'm running into issues with the proper referencing/nesting within the Range function below.

Sub ClearCells()

    Dim i As Long
    Dim sh As Worksheet

    Set sh = ThisWorkbook.Worksheets("VolJump")

    If sh.ListObjects.Count > 0 Then
        For i = 1 To sh.ListObjects.Count
            Range("Activesheet.ListObjects(1)[[Front Straddle]:[Front Option]]").Select
            Selection.ClearContents
        Next i
    End If

End Sub

VBasic2008 · Accepted Answer · 2021-04-26 10:13:50Z

3

Clear Contents of Table Columns Range

Option Explicit

Sub ClearCells()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("VolJump")
    Dim tbl As ListObject
    For Each tbl In ws.ListObjects
        ws.Range(tbl.Name & "[[Front Straddle]:[Front Option]]").ClearContents
    Next tbl
End Sub

answered Apr 26, 2021 at 10:13

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darren Bartrup-Cook · Accepted Answer · 2021-04-26 11:10:32Z

2

Just using the ListObjects:

Sub ClearColumns()

    Dim lo As ListObject
    Dim ColNum1 As Long, ColNum2 As Long
    For Each lo In ThisWorkbook.Worksheets("Sheet1").ListObjects
        'Get the index numbers of the start and end columns.
        ColNum1 = lo.ListColumns("Front Straddle").Index
        ColNum2 = lo.ListColumns("Front Option").Index
        
        'Resize the range from the start column to the end column and clear it.
        lo.DataBodyRange.Columns(ColNum1).Resize(, ColNum2 - ColNum1 + 1).ClearContents
    Next lo

End Sub

answered Apr 26, 2021 at 11:10

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

1 Comment

VBasic2008 Over a year ago

Forgot about Index. A valuable lesson (at least for me). Thanks.

Collectives™ on Stack Overflow

Reference a table by index number

2 Answers 2

Clear Contents of Table Columns Range

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Clear Contents of Table Columns Range

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related