Cells(1,1) vs Range("A1")

[NateO]

Hello again,

Huh? Wow, that is a big difference. But, on the other hand, humanly speaking, there is no difference.

Respectfully, I dissent. I'm not saying to discard the PasteSpecial Method, to any extent, but I am less than enamored with the fact that it actually changes the selected Range Object 'for you'. This bothers me to some extent...

And over a small range, it's well over twice as fast. So, in the case of working with small Range Objects, there are two benefits.

But, I guess my question is, "why?"

We're left to speculate, here. My conclusion? Working the Clipboard has a fair amount of overhead that is exposed when working with a small Range. But it's efficient enough where, over a large range, it can outpace Excel natively coercing a Range of data to a Variant Array in memory, etc...

So, I guess it does depend on what we are doing. Concur?

 

[Oorang]

Just another tip because I don't see anyone doing it in these posts.... Recalculate before loading your array
Turn calulation off
Turn Events Off
Load the array
Do your stuff
Plug your array back in
Turn the events back on
Turn the calculation back on

Don't think it matters? Try taking about 1000 records with and formulas. Run subtotal with and without calculation. I have seen differences of over 30 seconds.

 

[macleanb]

Good point - I know it matters, and I think we all must do this in our real work - but the noise this overhead puts into the results is major and should be excluded from the timings.

Still cant figure out why foo then bar is different from bar then foo?

Well I'm of down t'pub - have a good weekend chaps, by the time I come back this thread should have got to world peace/global warming - I am looking forward to it already

 

[NateO]

Just another tip because I don't see anyone doing it in these posts.... Recalculate before loading your array
Turn calulation off
Turn Events Off
Load the array
Do your stuff
Plug your array back in
Turn the events back on
Turn the calculation back on

Don't think it matters? Try taking about 1000 records with and formulas. Run subtotal with and without calculation. I have seen differences of over 30 seconds.

Quite right, it matters. I didn't mean to imply that we shouldn't do these things with production-level code/applications by omitting these techniques from my posts to this thread... In fact, if your project is distributed, I'd read the end-user's calculation setting into memory before toggling it in any direction.

My goal, in this case, was actually to expose the amount of screen redraw that the PasteSpecial Method was generating, thus inflating its resulting times, not to optimize. But, it seems that it's pretty efficient, even while performing this seemingly extraneous business, over a large Range.

So, I concur with the aforementioned advice.

Okay, this is in the Lounge (serious questions belong the Excel forum). macleanb, which pub are you going to? I celebrated St. George's Day, on Monday, at the Shepherd's Market in Mayfair. Cheers, mate.

 

[macleanb]

Cat & Canary, Canary Wharf (If I can get this trader of my back) - they serve a nice ale called fullers, "proper" bitter (well its not micro brewery/boutique, but a darn fine comercial ale)

 

[macleanb]

London Pride is a smooth and astonishingly complex beer, which has a distinctive malty base complemented by a rich balance of well developed hop flavours from the target, challenger and northdown varieties in the brew. At 4.1% a.b.v in cask (4.7% a.b.v in bottles)London Pride is an ideal session-strength premium ale.

I am not sure what "session strength" means, but I am about to try and find out!

mustn't get too larupped mind you...

 

[NateO]

Fullers, eh? I have heard of it, but I've yet to try it... I was near the Tower of London, the other day, in an older pub called 'Drawn and Quartered', I believe, and they were big on Fullers. Aside from serving it, they had Fullers ashtrays, etc...

I went with old faithful; Ice Cold Guinness.

First time in London... That's quite a town you have there. I think I only had about 10 near-death experiences while crossing the streets.

Have a nice weekend, everyone!

 

[ExcelChampion]

Hello again,

Huh? Wow, that is a big difference. But, on the other hand, humanly speaking, there is no difference.

Respectfully, I dissent. I'm not saying to discard the PasteSpecial Method, to any extent, but I am less than enamored with the fact that it actually changes the selected Range Object 'for you'. This bothers me to some extent...

And over a small range, it's well over twice as fast. So, in the case of working with small Range Objects, there are two benefits.

But, I guess my question is, "why?"

We're left to speculate, here. My conclusion? Working the Clipboard has a fair amount of overhead that is exposed when working with a small Range. But it's efficient enough where, over a large range, it can outpace Excel natively coercing a Range of data to a Variant Array in memory, etc...

So, I guess it does depend on what we are doing. Concur?

I completely concur...always have.

What do you mean by this, though:- "actually changes the selected Range Object 'for you'"

 

[Oorang]

I went with old faithful; Ice Cold Guinness.

Don't you know you're supposed to drink that warm?! I mean if you're that masochistic you should go the whole way

 

[erik.van.geit]

wow, you did post a lot here today
and only 70% off-topic

What do you mean by this, though:- "actually changes the selected Range Object 'for you'"

when you perform pastespecial the range where the data are pasted gets selected

Sub huh()
Range("A1").Select
Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox Selection.Address
End Sub

NOTE to all:
don't consider this as good code, it's only meant to show that A1 is not selected anymore: B1 is now