CODEWITHSUNDEEP
excelrangevba
Maulden
Maulden

Reputation: 1

How to reference Tables in VBA

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?

Upvotes: 0

Views: 3706

Answers (1)

PeterT
PeterT

Reputation: 8557

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

Upvotes: 1

Related Questions