13

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).

Community
  • 1
  • 1
eli-k
  • 10,898
  • 11
  • 40
  • 44
  • `Range(Cells([rowNum],[colNum]),Cells([rowNum],[colNum])`? – BruceWayne Jul 27 '17 at 16:31
  • Try that: `Range("Table1[[Column" & CStr(X) &"]:[Column" & CStr(X+d) & "]]").Select`. – Rafael Matos Jul 27 '17 at 16:32
  • 1
    @BruceWayne this would work but i'd have to mess around with row numbers, and so wouldn't be using the table features (eg. ability to select a table column) – eli-k Jul 27 '17 at 17:23
  • @RafaelMatos your suggestion would work if those were the actual column names. This may be likely in other cases as these are default names, in my case they were just an example, the actual names are different – eli-k Jul 27 '17 at 17:33

3 Answers3

20

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.

  • Resize... Nailed it!! – eli-k Jul 27 '17 at 17:38
  • I tried this code to copy specific columns from a table (3,4 and 6) but when I paste the result I get the columns 3,4,5 and 6. Why does that work for Select but not for Copy? – MikeSkril Apr 10 '23 at 19:48
  • I just realized that it actually works when I paste it into an Excel sheet. When I paste it into an HTML email (Outlook), it also pastes all columns between the selection. I really don't understand why this is happening... – MikeSkril Apr 10 '23 at 20:11
3

Use Columns method on DataBodyRange which can take a relative table range such as "A:B"

So if you wanted the first two columns you could write: ActiveSheet.ListObjects("Table1").DataBodyRange.Columns("A:B").Select

But what if you wanted to select based on a relative column number? Create a few functions to convert numbers to this string: 

Sub selectMultipe()
    ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(getRangeStr(1, 2)).Select
End Sub

'Get Range String
Function getRangeStr(startColNum As Long, endColNum As Long) As String
    startCol = ColLtr(startColNum)
    endCol = ColLtr(endColNum)

    getRangeStr = startCol + ":" + endCol
End Function

'Convert column number to letter
Function ColLtr(iCol As Long) As String
    If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function

Note: The column number to letter function was found here

jspek
  • 438
  • 3
  • 10
  • Typically, the 'column letter' is retrieved by splitting the absolute address on the *$* symbol and taking the second element of the array but that works too. –  Jul 27 '17 at 16:56
  • This is a little complex for my present needs, but might be useful in other situations - Thanks! – eli-k Jul 27 '17 at 17:40
1

Another way that I have used is this

ActiveSheet.Range("tblResult[" & .ListColumns(3).name & "]:tblResult[" & .ListColumns(.ListColumns.Count).name & "]").Select

which in this case selects column 3 and the remaining columns.

Jörgen
  • 11
  • 1