Replace cell address with Table Header reference

[gberg]

I have this code that I want to replace the "D2" cell reference with the header name of the table

With Cell Reference

Range(("D2"), Range("D2").End(xlToRight)).Select

I want something like this, but this gives me an error code

Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]", Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]").End(xlToRight)).Select

 

[zero269]

I have this code that I want to replace the "D2" cell reference with the header name of the table

With Cell Reference

Range(("D2"), Range("D2").End(xlToRight)).Select

I want something like this, but this gives me an error code

Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]", Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]").End(xlToRight)).Select

Hi gberg,

It's best to record a macro to see what the code should look like. However, I use code in some of my macros that reference Table Headers.

Try these different selection options. The first three are a range of contiguous Table Columns, while the last one is using a single Table Column. Just change my table name (t_Books) to your table name (TBL_Jan_JPR) and then in the VB Editor, use the F8 key to step through each selection. The last one will likely be what you want.

Sub Select_Table_Range()

  '#All Selects Table Headers, DatabodyRange and Totals row for specified range of Table columns
  Range("t_Books[[#All],[Title]:[Author]]").Select

  '#Headers selects Headers for specified range of Table columns
  Range("t_Books[[#Headers],[Title]:[Author]]").Select

  'Selects the DatabodyRange only for chosen Headers
  Range("t_Books[[Title]:[Author]]").Select
 
  'Selects a Single Header
  Range("t_Books[[#Headers],[Title]]").Select
 
End Sub

 

[kevin9999]

Please try the following (only slight change from what you had)

Range(Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]"), Range("TBL_Jan_JPR[[#Headers],[ACCEPTED CONTRACT]]").End(xlToRight)).Select

 

[gberg]

That worked, thanks for your help!

 

[kevin9999]

That worked, thanks for your help!

Happy to help, and thanks for the feedback