Excel Vba - selecting row of named range

Question

I have a currentregion, say, [A4:F200]. I select a cell anywhere in that range and want to then select that row of that range (not selection.row()) e.g. [D20].selected. Code to select [A20:F20]

This is the best I could come up with.

Dim iRw1 As Integer, iRw As Integer, rCurr As Range
Set rCurr = Selection.CurrentRegion
iRw1 = rCurr.Row
iRw = Selection.Row()
rCurr.Rows(1).Offset(iRw - iRw1).Select

Is there a better way? Is there a way to return the row number of the range? I googled endlessly but couldn't see anything to help me.

Thank you

Peter

iRw = Selection.Row() - iRw1 + 1 is another way, but any you have will require some math and knowing the first row of the currentregion. — Scott Craner
If D20 is selected and you want to select Range [A20:F20], then why not Range(Cells(selection.row, 1), Cells(selection.row, 6)) .. Your code will offset the selection -3 rows. So it will select A17:F17 — Naresh
...or Application.Intersect(Selection.Currentregion, Selection.Entirerow).Select — Tim Williams
Thanks Tim. A single line of code. Certainly the better way. Intersect! DOH!! — Peter Worthington

Tim Williams Tim Williams · Accepted Answer · 2020-03-31T04:34:29

0

votes

This should do it:

Application.Intersect(Selection.Currentregion, Selection.Entirerow).Select

Excel Vba - selecting row of named range

1 Answers