How to reference Tables in VBA

Question

I am placing a button on a sheet to allow to uppercase all items in two columns in a table.

Here is the code I have found elsewhere and adapted to try to make work:

Private Sub CommandButton1_Click()
 With Range("B10", Cells(Rows.Count, "B").End(xlUp))
        .Value = Evaluate("INDEX(UPPER(" & .Address(External:=True) & "),)")
    End With
With Range("C10", Cells(Rows.Count, "C").End(xlUp))
        .Value = Evaluate("INDEX(UPPER(" & .Address(External:=True) & "),)")
    End With
End Sub

I want the Range to reference Table2, columns 1 & 2 instead of B & C.

Suggestions?

peltiertech.com/structured-referencing-excel-tables half-way down that page there's a section on referencing tables from VBA — Tim Williams
– Tim Williams, Commented Oct 27, 2016 at 22:01

PeterT · Accepted Answer · 2016-10-27 20:34:23Z

1

For access to all sorts of table ranges and references, you need to use a ListObject. Here's an example:

Option Explicit

Sub test()
    Dim ws As Worksheet
    Dim t2 As ListObject
    Set ws = ActiveSheet
    Set t2 = ws.ListObjects("Table2")
    Debug.Print t2.ListColumns(1).Name

    Dim refRange As Range
    Set refRange = Union(t2.ListColumns(1).Range, t2.ListColumns(2).Range)
    Debug.Print refRange.Address
End Sub

answered Oct 27, 2016 at 20:34

PeterT

8,5971 gold badge19 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

How to reference Tables in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related