Avoiding Range("A1").Select

**j_Southern** · 01-20-2011, 11:42 AM

Some discussion in the "General" part of the Forum suggested that it is rarely neccessary to use Worksheet.Select or Range.Select. I can see why as it slows everything down and causes screen jumps. But how do I do it?

The bit of code below refreshes 2 queries in Sheet("Material_Calcs"). It then copies the dyamic table of the first query to "Raw_Materials and then appends the resuts of the second query to the table in "Raw_Materials.
How do I avoid all the sheet and range selections?

Please Login or Register  to view this content.

John

**Andy Pope** · 01-20-2011, 11:55 AM

You can normally condense the .Select and Selection parts.

Please Login or Register  to view this content.

If you are using simple copy/paste you can specify the paste location on the copy command.

Please Login or Register  to view this content.

I assume the non use of the variable 'y' is just a typo.

**Whizbang** · 01-20-2011, 12:59 PM

As far as screen jumping and slowing down is concerned, another great method, aside form limiting the use of .select, is to turn off screen updating.

Example:

Please Login or Register  to view this content.

This prevents the screen from refreshing at every little change and really speeds things up. Just remember to turn screen updating back on when you are done, otherwise the user may think that the workbook is frozen when it really is not.

**j_Southern** · 01-20-2011, 01:03 PM

Andy,
Thanks very much for your interest. I have tried your code and it works well noticeably increasing speed. I see the approach now
Am I right in assuming that when you refer to simple copy/paste you mean as distinct from PastSpecial, PasteFormulae etc?
The y variable is used further down the code. The John on the end of False is the typo! just checking
John

**Emilysouth** · 01-20-2011, 04:46 PM

Whizbang
I had not seen your contribution when I last replied. I can confirm that added to Andy Popes helpnit has speeded me up a treat.
I shall mark as solved. Thank you everybody for your help
John