Selection within a named range

A

Andy Chan

Dear all,

There is a named range "Rng" in my workbook. I want to write a VBA
program to select the first 10 rows of the range "Rng". What code should I
write?

Best Regards,
Andy
 
D

Dave Peterson

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("Rng").Areas(1).Resize(10)
Application.Goto myRng
End Sub

..areas(1) is nice if you have multiple areas in that range.

..resize(x,y) says to adjust the range to be x rows and y columns. If you omit
one of those x,y's, then it doesn't change that row/column

..resize(10) says 10 rows -- same number of columns
..resize(,4) says same number of rows -- 4 columns

..resize(11,33) says 11 rows -- 33 columns.
 
D

Don Guillett

another from anywhere in the workbook

Sub selectpartofnamedrng()
Application.Goto [rng].Rows("1:10")
End Sub
 
R

Ron Rosenfeld

Dear all,

There is a named range "Rng" in my workbook. I want to write a VBA
program to select the first 10 rows of the range "Rng". What code should I
write?

Best Regards,
Andy
Click to expand...

This seems to work also:

========================
Sub foo()
Dim SmallRange As Range

Set SmallRange = Range("Rng").Range(Cells(1, 1), Cells(10, 1))
Debug.Print SmallRange.Address
SmallRange.Select

End Sub
==========================

or, more simply doing just exactly what you requested:

=======================
Range("rng").Range(Cells(1, 1), Cells(10, 1)).Select
========================


--ron
 
A

Andy Chan

Thanks all!
I have completed my task!

Dave Peterson said:
Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("Rng").Areas(1).Resize(10)
Application.Goto myRng
End Sub

.areas(1) is nice if you have multiple areas in that range.

.resize(x,y) says to adjust the range to be x rows and y columns. If you
omit
one of those x,y's, then it doesn't change that row/column

.resize(10) says 10 rows -- same number of columns
.resize(,4) says same number of rows -- 4 columns

.resize(11,33) says 11 rows -- 33 columns.
Click to expand...
 
A

Andy Chan

Thanks all!
I have completed my task!
Ian said:
I don't know how you could do this, but you could create another named
range consisting of the area you want.
Click to expand...
 
A

Andy Chan

Thanks all!
I have completed my task!
Don Guillett said:
another from anywhere in the workbook

Sub selectpartofnamedrng()
Application.Goto [rng].Rows("1:10")
End Sub

--
Don Guillett
SalesAid Software
[email protected]
Andy Chan said:
Dear all,

There is a named range "Rng" in my workbook. I want to write a VBA
program to select the first 10 rows of the range "Rng". What code should
I write?

Best Regards,
Andy
Click to expand...
Click to expand...
 
A

Andy Chan

Thanks all!
I have completed my task!
Ron Rosenfeld said:
This seems to work also:

========================
Sub foo()
Dim SmallRange As Range

Set SmallRange = Range("Rng").Range(Cells(1, 1), Cells(10, 1))
Debug.Print SmallRange.Address
SmallRange.Select

End Sub
==========================

or, more simply doing just exactly what you requested:

=======================
Range("rng").Range(Cells(1, 1), Cells(10, 1)).Select
========================


--ron
Click to expand...
 
Top