Hi All

From reading this group and some of the MVP web sites I have come
across the use of SpecialCells and Intersect as the most efficient way
to loop through data in Excel VB logic.

What I have not found is a concise explanation of why this is so and
what exactly best practice is.

Can anyone point me to some good web resources that discuss the use of
SpecialCells and Intersect?

Cheers
Chrisso

On Mar 2, 6:08 am, "Chrisso" wrote:
Hi All

From reading this group and some of the MVP web sites I have come

across the use of SpecialCells and Intersect as the most efficient way
to loop through data in Excel VB logic.

What I have not found is a concise explanation of why this is so and
what exactly best practice is.

Can anyone point me to some good web resources that discuss the use of
SpecialCells and Intersect?

Cheers
Chrisso

Try the following:

http://www.mvps.org/dmcritchie/excel/proper.htm

-Tim

If you are using special cells, then there probably is not need to loop
through all cells - only cells that meet a specific criteria that can be
culled out by using special cells. So if say , column A contained constants
and formulas and you wanted to loop through just the formulas:

for each cell in columns(1).specialcells(xlFormulas)

The only advantage is that it reduces the number of cells that have to be
looked at and you don't have to test each cell to see if it contains a
formula. You already know it does, because you are only looking at cells
that contain fomrulas.

I wouldn't see it being any more involved than that. As to intersect,
again, it is a method to reduce the number of cells looked at. When used
with specialcells it is often used to correct a situation where you issue
specialcells and only one cell is usd to qualify the command. In that case,
specialcells will examine the entire usedrange of the worksheet. this is
only a problem is your code can't know in advance whether the range anchor is
one or multiple cells.

Public function Myfunc(rng1 as Range)
Dim rng as Range
set rng = Intersect(rng1,rng1.specialcells(xlConstants))
set myfunc = rng
End Function


if rng1 refers to a single cell, then rng1.specialcells(xlConstants) could
return many cells outside the calling routines interest. Using the intersect
culls it back down to the range of interest.
--
Regards,
Tom Ogilvy


"Chrisso" wrote:

Hi All

From reading this group and some of the MVP web sites I have come
across the use of SpecialCells and Intersect as the most efficient way
to loop through data in Excel VB logic.

What I have not found is a concise explanation of why this is so and
what exactly best practice is.

Can anyone point me to some good web resources that discuss the use of
SpecialCells and Intersect?

Cheers
Chrisso