I have refered to a range, where I do not know where the end is by
Set rng = Worksheets().Range(Cells(a,b), Cells(c,d)). This tends to be
flaky and I don't want to use the "A1" notation.

Any suggestions?

Set rng = Worksheets(1).Range(Cells(a,b), Cells(c,d))

is flakey when worksheets(1) isn't selected.

Cells(a,b) will refer to the activesheet (unless the code is in the worksheet's
sheet module--then it refers to the worksheet that owns the code).

Better to fully qualify the range:

Set rng = Worksheets(1).Range(worksheets(1).Cells(a,b), _
worksheets(1).Cells(c,d))

or with less typing (and fewer things to screw up):

with worksheets(1)
set rng = .range(.cells(a,b), .cells(c,d))
end with

Those dots mean that those ranges refer to the previous with object (in this
case, Worksheets(1).)



Sanj wrote:

I have refered to a range, where I do not know where the end is by
Set rng = Worksheets().Range(Cells(a,b), Cells(c,d)). This tends to be
flaky and I don't want to use the "A1" notation.

Any suggestions?

--

Dave Peterson

Where are you giving values to a,b,c,d and why don't you have
worksheet name in Worksheets()

--
Nori
-----------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...fo&userid=1936
View this thread: http://www.excelforum.com/showthread.php?threadid=37989