which range staement is preferable?
 

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
--


Gary

which range staement is preferable?
 

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.

"Gary Keramidas" wrote:

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
--


Gary

which range staement is preferable?
 

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)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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
--


Gary

which range staement is preferable?
 

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

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


"Bob Phillips" wrote in message
...
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)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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
--


Gary

which range staement is preferable?
 

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

--


Gary


"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

thanks barb

--


Gary


"Barb Reinhardt" wrote in message
...
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.

"Gary Keramidas" wrote:

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
--


Gary

which range staement is preferable?
 

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





"Gary Keramidas" wrote:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

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.


Gary Keramidas wrote:

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

--

Gary

"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson


--

Dave Peterson

which range staement is preferable?
 

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


"Tom Ogilvy" wrote in message
...
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





"Gary Keramidas" wrote:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

i see what you mean.

--


Gary


"Dave Peterson" wrote in message
...
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.


Gary Keramidas wrote:

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

--

Gary

"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson


--

Dave Peterson

which range staement is preferable?
 

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

--
Regards,
Tom Ogilvy


"Gary Keramidas" wrote:

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


"Tom Ogilvy" wrote in message
...
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





"Gary Keramidas" wrote:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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


"Dave Peterson" wrote:

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

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
--

Gary

--

Dave Peterson

which range staement is preferable?
 

Consider

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

and forget about having to qualify.

Alan Beban

Gary Keramidas wrote:
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

which range staement is preferable?
 

Just an addendum for Gary 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





"Alan Beban" wrote:

Consider

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

and forget about having to qualify.

Alan Beban

Gary Keramidas wrote:
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