Code to Select Multiple Columns in an Excel Table

Question

I am new to Excel VBA. I need a modification in my code so that I would be able to proceed further.

I want to select multiple table columns in an excel table. Here is my code:

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Sheets("Sheet1")
Set tbl = ws.ListObjects(1)

Range("tbl[[Column1]:[Column5]]").Select

When I put the table name, it works. but I want to use variable which I have used in my code to select the table columns.

Try: `Range(tbl & "[[Column1]:[Column5]]")` – Scott Craner Mar 16 '16 at 16:17 — Scott Craner, Mar 16 '16 at 16:17
thanx scott. it really helped me. – Salman Khan Mar 16 '16 at 17:00 — Salman Khan, Mar 16 '16 at 17:00

score 4 · Answer 1 · answered Dec 20 '16 at 16:12

4

You can use concatenation to use a variable as a table name.

Here is the code:

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Sheets("Sheet1")
Set tbl = ws.ListObjects(1)

Range(tbl & "[[Column1]:[Column5]]").Select

answered Dec 20 '16 at 16:12

Erin Strauts

61
4

XsiSecOfficial · Answer 2 · 2016-03-16T16:28:26.520

Welcome to Stackoverflow!

There are many ways to do this:

you could use:

Range("A:E").Select ' example selects columns from A to E

Otherwise you could also make it by using an array example:

Sub test()

 Dim x, y As Range, z As Integer
    x = Array(1, 5)
    Set y = Columns(x(0))
    For z = 1 To UBound(x)
        Set y = Union(y, Columns(x(z)))
    Next z
    y.Select
  End Sub

but this depends of what you needs

Best regards

Daniel

Code to Select Multiple Columns in an Excel Table

2 Answers2

Linked