The Range property

[paul.domaskis]

I am following http://www.anthony-vba.kefra.com/vba/vbabasic2.htm to
spin up on Excel/VBA programming.

Consider a case in which the Range keyword in the right-hand-side of
an assignment. Most of the examples in this page show arguments
supplied to Range to specify a matrix of cells on the spreadsheet. If
one does not supply arguments, and merely accesses the Cells function:

Range.Cells(2,5)

does that mean Range defaults to the entire spreadsheet?
Alternatively, is there a concept of "current/active Range", which
might (for example) be the last Range used in an expression? If so,
is this memory of the latest Range maintained on a worksheet-by-
worksheet basis, or is there just one "current/active" Range?

Do similar assumptions apply to a "current/active" WorkSheet?

Thanks!

 

[Dave Peterson]

That syntax didn't work for me.

But something like this would:

ActiveSheet.Range("A1").Cells(1,1)
Range("A1").Cells(1,1)

But I'm not sure I would use this very often.

Chip Pearson has some notes written by Alan Beban:
http://www.cpearson.com/excel/cells.htm

It may be an interesting read for you.

 

[joel]

the syntac should fo been something like this

Set MyRange = Range("D5:H10")
MyRange.Cells(1,2) = "XYZ"

Which is putting data into E5
Cells(1,2) is refering to Row 1 Column 2 of the area D5:H10

 

[paul.domaskis]

OK, got it. That site was useful in that it indicated to me that
single-indexing can refer to cells beyond the range from which Cells
was invoked.

However, there is still a scenario in which I would like to understand
whether there is a default range, and what that might be. If I use
Cells without a "Range." qualifier, what is the assumed range?

Thanks.

 

[paul.domaskis]

Thanks, Joel.

the syntac should fo been something like this

Set MyRange = Range("D5:H10")
MyRange.Cells(1,2) = "XYZ"

Which is putting data into E5
Cells(1,2) is refering to Row 1 Column 2 of the area D5:H10

 

[Dave Peterson]

If you use an unqualfied range object (either Range(...) or cells(...), then
it'll depend on where the code is located.

If the code is in a General module, then
Cells(2,26)
will refer to Column 26 (Z) and row 2 of the activesheet.

If the code is in a Worksheet module, then
cells(2,26)
will refer to Z2, but in the sheet that owns the code.

Essentially, it's Range("a1").cells(2,26)

But even though it's legal syntax-wise, it's not used very often.

On the other hand, something like:

Cells(x, 1).range("c1:e1")

is a nice way to loop (varying X) through rows and doing something to the cells
in column C:E of that row.

Dim X as long
with activesheet 'I'd recommend that you qualify all your ranges
for x = 3 to 8
if .cells(x,"A").value = 3 then
.cells(x,"A").range("c1:e1").clearcontents
end if
next x
end with

But there are lots of ways to do this same thing:

Dim myCell as range
dim myRng as range

with activesheet
set myrng = .range("a3:a8") 'or .range("a3").resize(6,1)
end with

for each mycell in myrng.cells
if mycell.value = 3 then
mycell.offset(0,2).resize(1,3).clearcontents
end if
next mycell

==========
My guess is that you'll code in what you like or find most friendly, but you'll
learn to read other's code so that you can either update it or learn from it.

 

[paul.domaskis]

Dave, thanks for all the hand-holding you've provided. I'm going to
have to take your last post in two sessions, but it looks like good
stuff. Just harkening back to my matlab days, I know there are many
subleties that one can exploit to advantage.