select a range w/in a range

[Guest]

Hi I have a maro w/ a named range from which I need to select 2 Nonadjacent
rows based on variables but can't seem to get the code right. I need to
select row$1, col_x and row_y, col_x of named_rng_z.

any help w/ the syntax would be appriciated

Zb Kornecki

 

[Guest]

Sub AAA()
Dim row1 As Long, row_y As Long
Dim col_x As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range
row1 = 3
row_y = 7
col_x = 2
Set rng1 = Range("named_rng_x")
Set rng2 = rng1(row1, col_x)
Set rng3 = rng1(row_y, col_x)
rng1.Parent.Select
Union(rng2, rng3).Select
End Sub

 

[moon]

Hi I have a maro w/ a named range from which I need to select 2
Nonadjacent
rows based on variables but can't seem to get the code right. I need to
select row$1, col_x and row_y, col_x of named_rng_z.

any help w/ the syntax would be appriciated

Zb Kornecki

Checkout the 'Union'-method, Zb.

Dim r As Range
Set r = Application.Union( _
ActiveSheet.Range("A1:E1"), _
ActiveSheet.Range("A4:E4"))
r.Select

 

[Guest]

Thanks Tom I follow what you're showing here except the Lines:
Set rng2 = rng1(row1, col_x)
Set rng3 = rng1(row_y, col_x)

this seems to Set the range to only one cell. How would I specify a ange of
cells?

thanks for your time
Zb

 

[Tom Ogilvy]

Sub AAA()
Dim row1 As Long, row_y As Long
Dim col_x As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range
row1 = 3
row_y = 7
col_x = 2
Set rng1 = Range("named_rng_x")
Set rng2 = rng1.Rows(row1)
Set rng3 = rng1.Rows(row_y)
rng1.Parent.Select
Union(rng2, rng3).Select
End Sub

 

[Guest]

Perfect thanks

Sub AAA()
Dim row1 As Long, row_y As Long
Dim col_x As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range
row1 = 3
row_y = 7
col_x = 2
Set rng1 = Range("named_rng_x")
Set rng2 = rng1.Rows(row1)
Set rng3 = rng1.Rows(row_y)
rng1.Parent.Select
Union(rng2, rng3).Select
End Sub

 

[Guest]

Thanks w/ your and Tom's Idea I got it to work perfect using Union method. I
just saved myself several hours a month writing out lists by hand