Clever Geek Handbook

Code to select multiple columns in an Excel table

I am new to Excel VBA. I need to change my code so that I can proceed further.

I want to select multiple columns of a table 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 name of the table it works. but i want to use the variable i used in my code to select the columns of the table.

+2


source to share


2 answers


Welcome to Stackoverflow!

There are many ways to do this:

you can use:

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

Otherwise, you can also do it 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 it depends what you need

Regards

Daniel

+1


source


You can use concatenation to use a variable as the 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
+1


source






More articles:


All Articles