VBA for selecting a number of columns in an excel table

eli-k picture eli-k · Jul 27, 2017 · Viewed 20.3k times · Source

As I learned here (also quoted in SO) the following code can be used to select the data-body of column 3 in Table1:

ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select

I need help to select a number of columns together - say columns 3 to 5, or columns X to X+3 .

Using answers to this question I manged to go halfway by using actual column names:

Range("Table1[[Column3]:[Column5]]").Select

But I need to be able to use column numbers instead of names, as they will be the result of a function (i.e. columns X to X+d).

Answer

user4039065 picture user4039065 · Jul 27, 2017

For a contiguous range, simply resize a single column.

ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Resize(, 3).Select

For a more complex selection, use Union to collect them prior to the .Select process.

With ActiveSheet.ListObjects("Table1")
    Union(.ListColumns(3).DataBodyRange, _
          .ListColumns(4).DataBodyRange, _
          .ListColumns(5).DataBodyRange).Select
End With

See How to avoid using Select in Excel VBA macros for better methods.