Select Named Range in current cell row

[jared.meyering]

This is a cross post from http://www.excelforum.com/excel-programming/776843-select-named-range-in-current-cell-row.html It has become buried over there so I thought I'd ask for some help over this way.

I have a sheet with names and other identifying data along the left hand columns and a named range at the top of my sheet showing date information (ie 1-31). And I have named the date range "DateAll"

I've been trying to write a macro that gets the DateAll values for specific individuals that become selected during an automated search.

The problem is that I'm not sure in the future if I will be adding additional information on the individuals, so I need to select the specific columns associated with the DateAll range but the range needs to be selected in the activerow so even if the column labels change in the future it will still grab the right range

Here are some things I've tried and failed.

Code:

LineHours = FirstMatch.Offset(-1, -6).Range("DateAll").Select

This did work but when I added new columns it failed by selecting two columns too far. I'm also not sure why it worked. I dont know why I had to have (-1, -6) to select the current row...but anyway

Code:

LineHours = FirstMatch.Row.Range("DateAll")

Obviously returns an error...


In plain English I would like the code to select the cells related to the column's in range "DateAll" in the Selected cell's row. And I would like it to select that range no matter the number of columns added or removed.

Thanks in advance. I've been banging my head against a wall for a few days now.

 

[Jerry Sullivan]

Welcome Jared,

Try:

With Range("DateAll")
    Set rngX = .Resize(1).Offset(ActiveCell.Row - .Row, 0)
End With

 

[jared.meyering]

Perfect!!!! Thanks a lot!

 

[Jerry Sullivan]

Glad that helped Jared. Thank you for noting this has been solved on the cross-post thread.