which range staement is preferable?

[Gary Keramidas]

may have asked this before, but which of these is preferable? (disregarding the
select<g>)

ws.range(ws.cells(2,1),ws.cells(3,1)).select
ws.range(cells(2,1),cells(3,1)).select
range(ws.cells(2,1),ws.cells(3,1)).select

 

[Guest]

It appears that any of them work. I happen to prefer

ws.Cells(2, 1).Resize(2, 1).Select

I don't know if one is more efficient than the rest however.

 

[Bob Phillips]

Gary,

I would use

With ws
.Range(.cells(2,"A"),.cells(3,"A")).Select
End With

I prefer using the column letter if I know it, obviously if it is being
dynamically set, the number works better.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

The top one is my favorite of your 3 choices--only because the bottom two may
fail.

this one:
ws.range(cells(2,1),cells(3,1)).select
didn't qualify the cells().

This one:
range(ws.cells(2,1),ws.cells(3,1))
didn't qualify the range().

It'll depend on where the code is and what the activesheet is and what ws is.
If you hit the perfect storm, you may have to spend time debugging.

 

[Guest]

the summation.

If you qualify one, qualify all. If not, qualify none (choosing something
in between is a waste of time and effort).

--
Regards,
Tom Ogilvy

The top one is my favorite of your 3 choices--only because the bottom two may
fail.

this one:
ws.range(cells(2,1),cells(3,1)).select
didn't qualify the cells().

This one:
range(ws.cells(2,1),ws.cells(3,1))
didn't qualify the range().

It'll depend on where the code is and what the activesheet is and what ws is.
If you hit the perfect storm, you may have to spend time debugging.

 

[Gary Keramidas]

thanks, bob, i usually use the column letter when i can, too, but just stuck
something together about how to qualify the ranges. it's easier for me to debug.

 

[Gary Keramidas]

thanks dave and tom. they all seemed to work for me, but i guess qualifying them
every range is the best bet.

--


Gary

the summation.

If you qualify one, qualify all. If not, qualify none (choosing something
in between is a waste of time and effort).

 

[Gary Keramidas]

thanks barb

--


Gary

It appears that any of them work. I happen to prefer

ws.Cells(2, 1).Resize(2, 1).Select

I don't know if one is more efficient than the rest however.

 

[Guest]

Use this in a general code module when ws doesn't refer to the activesheet

Dim r as Range

Set r = ws.range(cells(2,1),cells(3,1))


use either of these in a worksheet module where ws isn't the worksheet
containing the code

Dim r as Range
set r = ws.range(cells(2,1),cells(3,1))
set r = range(ws.cells(2,1),ws.cells(3,1))

--
Regards,
Tom Ogilvy

thanks dave and tom. they all seemed to work for me, but i guess qualifying them
every range is the best bet.

--


Gary

the summation.

If you qualify one, qualify all. If not, qualify none (choosing something
in between is a waste of time and effort).

 

[Dave Peterson]

Try stepping through this in a general module:

Option Explicit
Sub testme()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim rng As Range

Set ws = Worksheets.Add
Set ws1 = Worksheets.Add

ws1.Select
'this works
Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(3, 1))

'how about:
Set rng = ws.Range(Cells(2, 1), Cells(3, 1))

'how about
Set rng = Range(ws.Cells(2, 1), ws.Cells(3, 1))
End Sub

Now copy the exact code and put it behind a worksheet and step through it.

You may want to comment out the failing step and rerun to get to the other
lines.

thanks dave and tom. they all seemed to work for me, but i guess qualifying them
every range is the best bet.

 

[Gary Keramidas]

in these 2 examples, it's not going to hurt anything if the range is qualified
again, is it? sometimes i just do it for visual purposes, but it's not needed.

With ws4.Range("A1:O" & nextrow)
.Sort Key1:=ws4.Range("A2")


With ws3.Cells(rngfound.Row, iMonth)
.Value = ws3.Cells(rngfound.Row, iMonth) + arr2(i)


--


Gary

Use this in a general code module when ws doesn't refer to the activesheet

Dim r as Range

Set r = ws.range(cells(2,1),cells(3,1))


use either of these in a worksheet module where ws isn't the worksheet
containing the code

Dim r as Range
set r = ws.range(cells(2,1),cells(3,1))
set r = range(ws.cells(2,1),ws.cells(3,1))

--
Regards,
Tom Ogilvy

thanks dave and tom. they all seemed to work for me, but i guess qualifying
them
every range is the best bet.

 

[Gary Keramidas]

i see what you mean.

--


Gary

Try stepping through this in a general module:

Option Explicit
Sub testme()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim rng As Range

Set ws = Worksheets.Add
Set ws1 = Worksheets.Add

ws1.Select
'this works
Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(3, 1))

'how about:
Set rng = ws.Range(Cells(2, 1), Cells(3, 1))

'how about
Set rng = Range(ws.Cells(2, 1), ws.Cells(3, 1))
End Sub

Now copy the exact code and put it behind a worksheet and step through it.

You may want to comment out the failing step and rerun to get to the other
lines.

 

[Guest]

No - should be no problem - Unless a reference is preceded by a period, it is
not affected by the With statement.

--
Regards,
Tom Ogilvy

in these 2 examples, it's not going to hurt anything if the range is qualified
again, is it? sometimes i just do it for visual purposes, but it's not needed.

With ws4.Range("A1:O" & nextrow)
.Sort Key1:=ws4.Range("A2")


With ws3.Cells(rngfound.Row, iMonth)
.Value = ws3.Cells(rngfound.Row, iMonth) + arr2(i)


--


Gary

Use this in a general code module when ws doesn't refer to the activesheet

Dim r as Range

Set r = ws.range(cells(2,1),cells(3,1))


use either of these in a worksheet module where ws isn't the worksheet
containing the code

Dim r as Range
set r = ws.range(cells(2,1),cells(3,1))
set r = range(ws.cells(2,1),ws.cells(3,1))

--
Regards,
Tom Ogilvy

 

[Alan Beban]

Consider

Set rng = ws.Range("A1")
Range(rng(2,1), rng(3,1)).Select

and forget about having to qualify.

Alan Beban

 

[Guest]

Just an addendum for Gary re: Alan's post:

That certainly works in a general module, but breaks in a worksheet module
when ws isn't the worksheet containing the code.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng as Range, r as Range
Set ws = Me.Next
Set rng = ws.Range("A1")
Set r = Range(rng(2, 1), rng(3, 1))
MsgBox r.Address(0, 0, xlA1, False)
End Sub

fails for the same reason your (Gary's) third example failed in a worksheet
module.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng as Range, r as Range
Set ws = Me.Next
Set rng = ws.Range("A1")
Set r = rng.Parent.Range(rng(2, 1), rng(3, 1))
MsgBox r.Address(0, 0, xlA1, False)
End Sub

does work.

--
Regards,
Tom Ogilvy