Select all cells in a row to the last column header, even if not contiguous

[VBAProIWish]

Hello All,

I need code that selects all columns in a row to the last column header, regardless of what cell I start in.
I tried the macro recorder...

  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlToRight)).Select

...but depending on which column I start in, the number of times I would need to use...

Range(Selection, Selection.End(xlToRight)).Select

...would vary, so I can't use it.


I figured examples would further help to explain what I am looking for, see below...
Note that the grey cells would be the selected cells

All the same would be true if I started in column B (or whatever other column)

Some Notes:
>In the examples above, there are 6 columns, but the number of columns and the column starting point will vary, so the code would need to reference the last column header as "Discount" and not as "column F"

>This would be from the active workbook and active worksheet, whatever the names would be.


I hope this isn't too challenging. I searched for over an hour on the forum this morning and to my surprise, I couldn't find anything that matched my needs.

The only pieces of code I found and tried to use is the below but I just couldn't get the syntax correct...

Sub Select_All_Rows_to_last_column()
Set wk = activeworkbook
Dim lc As Long
lc = ws.Columns.End(xlToRight).Column

Range("A2").Select
Range(ws.Cells(lc)).Select

End Sub

Thanks much for any help anyone can provide!

 

[Georgiboy]

Maybe the below will help:

Sub test()
    Dim LastHead As Long
    
    LastHead = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Selection, Cells(Selection.Row, LastHead)).Select
End Sub

 

[Legacy 143009]

Are you looking for something like this:

Range(Selection, Cells(Selection.Row, Cells(1, Columns.Count).End(xlToLeft).Column)).Select

 

[VBAProIWish]

Wow, that was fast and they both worked! Thank you both!

What if I want to find the last column with data by column name instead?

 

[Legacy 143009]

Range(Selection, Cells(Selection.Row, Application.Match(data, Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft).Column), 0))).Select

I haven't tested but this may work.

 

[Georgiboy]

Mine would be:

Sub test()
    Dim LastHead As Long
   
    LastHead = Rows(1).Find("Word").Column
    Range(Selection, Cells(Selection.Row, LastHead)).Select
End Sub

Or

Sub test()
    Range(Selection, Cells(Selection.Row, Rows(1).Find("Word").Column)).Select
End Sub

 

[VBAProIWish]

Thanks to both!

The answer is this one here...

Sub test()
    Range(Selection, Cells(Selection.Row, Rows(1).Find("Word").Column)).Select
End Sub

 

[Georgiboy]

You're welcome, glad we could help.