英文:
Select a table range with the name of the columns
问题
I need to select a range (multiple columns) inside a table, and to do so, I use the name of the column. The table may change in size, so I thought 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 characters "[" and "]". For example, right now my last column is equal to "Collins, Sacha [2]".
Normally 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 don't know how to solve it without changing the logic.
I could add a column before selecting the range, clear it, and then 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 
英文:
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
答案1
得分: 2
A bit cleaner:
Dim table_roses As ListObject
Set table_roses = ActiveSheet.ListObjects(1)
'从“Total”选择到表格末尾
With table_roses.ListColumns("Total")
.Range.Resize(, 1 + table_roses.ListColumns.Count - .Index).Select
End With
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论