Status Bar with For Each Stmt

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

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
 
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
 
Use the UsedRange Count property to get a total of cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
But you gave him the answer anyway :-)

--

HTH

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