Solved: ?Cells? vs. ?Range?

[Cyberdude]

I seldom (or should I say ?celldom??) use ?Cells? when coding, primarily because to me it doesn?t document as well as using ?Range?, plus I hate having to reverse the order of arguments for columns and rows in my mind. The VBA Help provides the following example of "Cells" use for setting the font style of cells ?A1:C5? to be Italic:
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True I would have written
Range("A1:C5").Font.Italic = True My question is: Are there any clear reasons why ?Cells? should be used at times instead of ?Range?? To what degree is the use of ?Cells? a matter of personal preference versus a requirement for writing the statement? How do YOU decide which to use?

[Jacob Hilderbrand]

I generally use Range, unless the column is a variable number, then Cells is used.

[johnske]

Ditto (what Jake said) :)

[malik641]

What Jake said.

And I like SpecialCells, so I use cells to get the last row of the last cell:

Range("A1:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)

That's the way I prefer to use it, anyway. I'm sure somebody has a better way than this....but I like it.

[mdmackillop]

I'm bicellular and can swing either way. I wouldn't use Range with two column variables though. There are limits!

[Bob Phillips]

I am firmly of the Cells camp, I rarely use Range in that way.

I find Cells a lot simpler to work with variables, such as


Cells(Rows.Count,"A").End(xlUp).Row

rather than


Range("A" & Cells(Rows.Count).End(xlUp).Row)

The latter feels klunky to me, I hate the thought of using & in such a construct, and I bet it is less efficient (building a string, rather using Longs).

I also prefer it when looking to set a variable range, using Resize to handle the variable part


Set rng = Range(Range("A" & startRow), Range("A" & startRow).End(xlDown))
Set rng = Range(rng, rng.End(xlToRight))
MsgBox rng.Address

Set rng = Cells(startRow, "A").Resize(Cells(startRow, "A").End(xlDown).Row, _
Cells(startRow, "A").End(xlToRight).Column)
MsgBox rng.Address

To me, rows, columns seems the correct order.

[compariniaa]

if I'm working with ranges, i use just the Range (unless there are too many variables in the references), but I try to use cells, because even if I don't know the column number you could still do something like
cells(5,"AC") rather than counting all the way out to column AC to get the column number

[Norie]

Cyberdude

I prefer using Range, but have no objection to Cells.

One thing about your example and constructs like it is that you would need to be sure that Range/Cells were referring to the same worksheet, or ensure that there was a worksheet reference.

With Worksheets("Sheet1")
.Range(.Cells(1, 1), .Cells(5, 3)).Font.Italic = True
End With

[matthewspatrick]

As with most of the above, if I have to use variables, I often go with Cells(); if I know I want to work on a particular range, I often go with Range() or even the lazy expedient [range].

[Zack Barresse]

I'm with Bob on this one, mainly in the Cells camp. I do use Range, however it's less often these days. And I never use shorthand notation anymore.

[Bob Phillips]

And I never use shorthand notation anymore.

Nor I, it is an abomination which should have been drowned at birth :devil2:

[mdmackillop]

So what's wrong with shorthand?

[Zack Barresse]

The fact that you can't mix variables with it is plenty enough for me. Besides that I think it confuses people and it's non-standard. Just my humble opinion. :)

[Bob Phillips]

No, it's a fact!

And it is less efficient.

[Zack Barresse]

No, it's a fact!

And it is less efficient.
I love your quaintness Bob. :devil2:

[malik641]

I'm sorry......shorthand notation? I've probably seen that but didn't know that's what it was called....what is that?

[mdmackillop]

[A1] for example.

[malik641]

OOOOooooh, ok. Yeah, I've seen that before. Never use it though. And by what I'm reading I'm glad I don't.

[Zack Barresse]

My biggest peeve with SN (Shorthand Notation) is the non-mixing of variables with it. For instance, try these ...

[Ai] = 1
[A & i] = 1
[A & Clng(i)] = 1

You'll get a 424 runtime error everytime it comes to those lines even though it won't flag a compile error.

[mdmackillop]

I don't know about efficency, but I often use it to increase mine; it's shorthand, and I'm a lousy typist!

[Cyberdude]

Fascinating responses! Thank you all for your great opinions on this. You gave me some good insights. :bow:

[Killian]

Cells, for sure.
Although in disposable code, laziness on my part occasionally results in Range, SN and countless other abominations. :whistle:

[austenr]

:rofl: Me too Malcomb.

[Bob Phillips]

:rofl: Me too Malcomb.

Rubbish, you even added an extra letter to his name :rotlaugh:

[mdmackillop]

Close Bob, but no cigar!
Regards
Malcolm

[shades]

When I first starting using VBA, the only thing I saw was "Range" examples. However, after reading Bill Jelen's examples (and others) in VBA and Macros for MS Excel I began using "Cells" and saw many of the advantages noted in above discussion. So right now, I have a mixed bag. (Cornfusing ain't it? :bug: :rotlaugh: ) As I write new code, however, I am using "Cells" much more often.

Tough habit to break though, especially when you get old...:p

I think I should hang out more with Bob and Zack and ...:beerchug:

[Bob Phillips]

Close Bob, but no cigar!
Regards
Malcolm

Didn't notice the missing one :doh:

[malik641]

I think I should hang out more with Bob and Zack and ...:beerchug:
I've been wondering, do the members here ever get together for like a seminar or something?

[Zack Barresse]

Not really Joseph. There just isn't the overhead structure for it, not the finances available. Since we are spread out all over the world, it gets tough. I have met with a few of the members here, but it was all happenstance. I met mvidas when he took a trip around the country. He's a great guy, and super funny in person. (No, I was retarded and didn't get pictures.) I've also met Bob, Bill Jelen, Colo and a slew of other MVP's (I crashed their party, prior to being an MVP, hehehe). I'd like to go to the Excel User Conference in October (18-20 in Hell-A).

I've met someone on here recently that actually lives 20 minutes from me in Hermiston! (Their username is, coincidentally, hermiston (http://www.vbaexpress.com/forum/member.php?u=6110). LOL!) Over at MrExcel board, I've met a guy who was about an hour to the other side of me, and a guy from the valley (2-3 hour drive) who said he'd stop by and let me buy him a drink. :cool: Not too sure about other people though. I do know that Dreamboat and Scott met with Bill Jelen one time in New York City.

I don't know why but I think it's really cool to meet people you know in person, especially when they live so close! It's not a very good view, but you can see globally where our members are (http://www.vbaexpress.com/forum/worldmap.php), or see a list of locations (http://www.vbaexpress.com/forum/memberlocations.php).

[Bob Phillips]

I've also met Bob, Bill Jelen, Colo and a slew of other MVP's (I crashed their party, prior to being an MVP, hehehe).

Including Andy Pope and Jon Peltier who post here.

[malik641]

Since we are spread out all over the world, it gets tough. That's what I was thinking. Sounds like you've met a lot of people, that's cool :). I haven't met anyone from here yet, but I'd like to. Who knows what the future will bring.

Thanks for the map and list, didn't know they existed here at vbax. It looks like if you put an island between US and UK it would probably be packed with members, lol

[Cyberdude]

Zack, it seems curious to me that there are so many members in the U.S., yet I would never have guessed it by the number who are posting. Incidentally, if you ever find yourself crusing down the 101 freeway near San Jose, I'm about a mile off the 101 in Morgan Hill. Give us a call!

[geekgirlau]

Well given that my little red dot is about as far away from you guys as is physically possible, I don't think I'll be running into many of you by accident.

However I'm planning a trip to Ireland at end of '07 (waiting until the rugrats are a bit older), and will be looking for a suitable conference going on in that part of the world at the right time (holiday semi-courtesy of the tax man!).

[Bob Phillips]

We are talking about holding an Excel conference in Hong Kong or Sydney next year. From what we are told, Hong Kong would be better as it is easy to get to from Oz, and it opens up to a large Chinese market.

Would that interest you?

[geekgirlau]

Hmmm, if I'm going go Ireland I'd prefer somewhere in Europe - maybe I can class the Hong Kong leg of the journey as a business trip :conscious

[johnske]

...
Range("A" & Cells(Rows.Count).End(xlUp).Row)

The latter feels klunky to me, I hate the thought of using & in such a construct, and I bet it is less efficient (building a string, rather using Longs)...That's because it is clunky Bob, in fact I think you'll get an error, try using Range("A" & Rows.Count).End(xlUp).Rowinstead. :devil2:



The fact that you can't mix variables with it is plenty enough for me. Besides that I think it confuses people and it's non-standard. Just my humble opinion. :):stir: It was never intended that variables be mixed with SN, it's used purely to refer to ranges/range values and if you're coming straight from worksheet coding to VBA coding, then = [A1] should be far more intuitive for you to use than = Range("A1") {c.f. = A1 in s/s code, = [A1] in VBA}.

Range? Exactly what the hell does that mean? It's a distance to somewhere is it? Oh, do you mean the worksheet co-ordinates Column A and Row 1 ... once you get used to this weird terminology, the devilish Range("A1") notation can then be used to mix variables with range objects.

:yeahright Steel/alloy wheels with pneumatic tyres are also non-standard (wonder how much petrol we'd save these days if all cars had wooden spokes and rims and steel tyres) Interesting that M$ peddles such an abomination then, even persisting with it right up to Office 2003 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlhowReferringtocellsusingshortcutnotation1_HV05204430.asp).

SN, Range, Cells, everything has a use and purpose and it'd be best if we learnt and used all of them where convenient or appropriate rather than saying "Oh, I'm {not familiar with}/{don't like} that so I'm not going to use it". :2p:

[Bob Phillips]

SN, Range, Cells, everything has a use and purpose and it'd be best if we learnt and used all of them where convenient or appropriate rather than saying "Oh, I'm {not familiar with}/{don't like} that so I'm not going to use it".

AFAIR nobody took that stance, we gave perfectly good reasons why SN is flawed and why we don't use it, and why we found Range to be less flexiblke than Cells and so we rarely use that.