dealing with blank cells in a range

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:D" & 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?

Hello Steve,

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

Sincerely,
Leith Ross

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

Would probably be the easiest.

--
Regards,
Tom Ogilvy


"steve" <[email protected]> wrote in message
news:[email protected]...
> 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:D" & 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?

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" wrote:

> rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
> rng.Address(1, 1, xlA1, True) & _
> ",5)))")
>
> Would probably be the easiest.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "steve" <[email protected]> wrote in message
> news:[email protected]...
> > 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:D" & 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?
>
>
>

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.

--
Regards,
Tom Ogilvy


"steve" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
> > rng.Address(1, 1, xlA1, True) & _
> > ",5)))")
> >
> > Would probably be the easiest.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "steve" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:D" & 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?
> >
> >
> >