VBA to select several rows in named range

[tttommy2]

Hi there

I hvae a named range "MyRange". I want to select rows 10 to 16. I'd have thought that

Range("MyRange").Rows(10:16).Select

would work. However it stops my Macro.

Any ideas please?

Thank you.

T

 

[frank_AL]

This should do the trick for you

Sub selectrange()
Dim fr As Long
Dim lr As Long


fr = Range("MyRange").Row
lr = Range("MyRange").Cells(1, 1).Row + Range("MyRange").Rows.Count - 1


Worksheets("Sheet1").Rows(fr & ":" & lr).Select


End Sub

 

[Akuini]

Maybe this:

Range("MyRange").Rows("10:16").Select

 

[Norie]

This works for me provided the sheet with named range 'MyRange' on it is the active sheet and that the named range has workbook scope.

Range("MyRange").Rows("10:16").Select

 

[tttommy2]

Thank you Akuini and Norie. That works great.


How would I take this further using a variable for the Row numbers?

A1 = 10
B1 = 16

Dim i as long, j as long
i = Range("A1")
j= Range ("B1")

Range("MyRange").Rows("i:j").Select

Thanks
tttommy2

This works for me provided the sheet with named range 'MyRange' on it is the active sheet and that the named range has workbook scope.

Range("MyRange").Rows("10:16").Select

 

[Norie]

To use variables try something like frank_AL suggested but replace fr and lr with i and j respectively.

Dim i as long, j as long

    i = Range("A1").Value
    j= Range ("B1").Value

    Range("MyRange").Rows(i & ":" & j).Select

 

[tttommy2]

Thank you Norie and thank you frank_AL

tttommy2

To use variables try something like frank_AL suggested but replace fr and lr with i and j respectively.

Dim i as long, j as long

    i = Range("A1").Value
    j= Range ("B1").Value

    Range("MyRange").Rows(i & ":" & j).Select