VBA to select number of columns in excel table
As I found out here (also quoted on 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 choosing the number of columns together - say columns 3 through 5, or columns X through X + 3.
Using the answers to this question I was able to get halfway through using the actual column names:
Range("Table1[[Column3]:[Column5]]").Select
But I need to be able to use column numbers instead of names, since they will be the result of the function (i.e. columns from X to X + d).
source to share
For a contiguous range, just resize one column.
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Resize(, 3).Select
For more complex selections, 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 Excel VBA Select Macros to Improve Methods.
source to share
Use the Columns method on the DataBodyRange which can take a relative range of tables like "A:B"
So, if you want the first two columns, you can write: ActiveSheet.ListObjects("Table1").DataBodyRange.Columns("A:B").Select
But what if you want to select based on the relative column number? Create some 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 the literal function was found here
source to share