dealing with blank cells in a range

[Guest]

i look for the max number in a range of codes in the following format:
E06001
E06002...

here is a section of my code:

Set rng = destWB.Worksheets("Sheet1").Range("D5" & Lr)

rmax = Application.Evaluate("MAX(VALUE(RIGHT(" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

****how can i tell it to ignore blank cells when looking for the max?

 

[Tom Ogilvy]

rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

Would probably be the easiest.

 

[Leith Ross]

Hello Steve,

What impact do the blank cells have on determining the MAX value in the
Range?

Sincerely,
Leith Ross

 

[Guest]

that works....but i'm confused...what exactly does that do??

leith....
if there is a blank cell, i get an error (type mismatch)

 

[Tom Ogilvy]

During the evaluation, It prepends 5 zeros to the left side the value of
each cell to guarantee each cell is at least 5 characters - this would
result in a zero value for blank cells and would not alter any values.