Select a table range with the name of the columns

Question

I need to select a range (multiple column) inside a table and to do so, I use the name of the column. The table may change in size so I though this was an easy way to do it.

Dim first_col_index As Integer

first_col_index = table_roses.ListColumns("Total").Index

ws_roses.Range(table_roses & "[[" & table_roses.ListColumns(first_col_index + 1) & "]:[" & table_roses.ListColumns(table_roses.ListColumns.Count) & "]]").Select

Application.Selection.Clear

This way might not be the best or the most elegant but it works.

My problem is that sometimes table_roses.ListColumns(first_col_index + 1) and/or table_roses.ListColumns(table_roses.ListColumns.Count) contain the caracter [ and ]. For exemple, right now my last column is equal to "Collins, Sacha [2]".

Normaly I would just add a backtick to solve the issue like so: =SOUS.TOTAL(9;table_roses[Caron, Luc '[2']]) but now it's inside a variable...

I understand my issue but I dont know how to solve it without changing the logic.

I could add a column before selecting the range, clear it and the remove that column, but I wanted to know if there is a more elegant way to do it!

(Keep in mind that I'm still new to VBA)

Thank you in advance :)

`Replace(table_roses.ListColumns(first_col_index + 1), "[", "'[") ` etc — Tim Williams, Apr 19 '23 at 16:16
Recommended reading: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Apr 19 '23 at 16:28

score 2 · Accepted Answer · answered Apr 19 '23 at 16:25

2

A bit cleaner:

Dim table_roses As ListObject

Set table_roses = ActiveSheet.ListObjects(1)
    
'select from "Total" to the end of the table
With table_roses.ListColumns("Total")
   .Range.Resize(, 1 + table_roses.ListColumns.Count - .Index).Select
End With

answered Apr 19 '23 at 16:25

Tim Williams

154,628
8
97
125

Added Offset(1, 0) to exclude the header and it works wonderfully! Thank you :) – Sacha Collins Apr 19 '23 at 18:02
Or use `.DataBodyRange.Resize(...)` which deosn't include the header. https://learn.microsoft.com/en-us/office/vba/api/excel.listcolumn.databodyrange – Tim Williams Apr 19 '23 at 18:04

Select a table range with the name of the columns

1 Answers1