Status Bar with For Each Stmt

[ExcelMonkey]

I usually use status bars with For Next loops as I always
know how many loops will occur in my statement (i.e. For x
= 1 to 100 tells me there will be 100 loops).

Thus X/100 tells me my percentage completed.

However I have a For Each loop that checks cells in a Used
range of a worksheet. I am assuming that I can back out
the number of colums and rows (c*r=#cells) within the
UsedRange property to also build a status bar.

For Each cell In sh.UsedRange

Next

Does anyone know how to do this? Thanks

 

[Simon Livings]

Without wishing to be flippant, why don't you just increase a variable
as a counter and use this instead. This is probably easier than trying
to backsolve the cell number in the range. eg

For Each cell in sh.UsedRange
i=i+1
Application.StatusBar=format(i/sh.usedrange.cells.count,"0%")
next cell

 

[Bob Phillips]

Use the UsedRange Count property to get a total of cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Simon Livings]

Sorry - misunderstood which part of the code you were lacking.

 

[Bob Phillips]

But you gave him the answer anyway

--

HTH

RP
(remove nothere from the email address if mailing direct)