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

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

--
Regards,
Tom Ogilvy


"Zb Kornecki" wrote:

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

"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet schreef in bericht
...
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

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" wrote:

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

--
Regards,
Tom Ogilvy


"Zb Kornecki" wrote:

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

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


--
Regards,
Tom Ogilvy



"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Zb Kornecki" wrote:

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

Perfect thanks

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy



"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Zb Kornecki" wrote:

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

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

"moon" wrote:


"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet schreef in bericht
...
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