Hi All......

Trying to make a chart (actually dozens)with a macro(s) using Autofiltered
data of unknown range.
This code works fine to adjust the range, except the values in AA are result
of IF formula with "" as one option and if the bottom row of the column AA
has the "", the whole series does not chart. If the result of the bottom
line of AA is a number, even zero, all is well.

Sheets("INquiry").Select
lastrow = Cells(Rows.Count, "AA").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AC9:AC" & lastrow)

I don't want to replace the "" with a zero because I use the formula
results for other things besides just drawing a chart and don't want zeros
there. I suppose I could use helper columns and substitute the zero for the
"" there and use them to draw the charts, but that seems kinda "brute
force".....even for ME <G.

Oh yeah, I've also even tried changing the "Cells(Rows.Count, "AA")" to an
adjacent column that does have a number in that bottom row, but no
joy.....it's still the same.

Please if anyone has an idea about how to modify the code, so that the
series would chart even tho the bottom row is a "", I would muchly
appreciate. It's ok if the "" plots as a 0, all the others within the series
do that and it's fine.....it's just the bottom one that's causing me grief.

TIA
Vaya con Dios,
Chuck, CABGx3

Hi All.......

So, I ran out of time and had to use a work-around. For those interested, I
modified the IF formulas to replace the "" with a very small number I hoped
would never show up in my data, .0000007. This allowed the dynamic range
code to work, and a zero was plotted for these values, which is ok. I then
modified by other formululas to ignore those cells if they contained that
special value of .0000007. It seems to work for now.

Just posting back to let anyone who might have been working on the problem
that it's been worked-around.


Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Hi All......

Trying to make a chart (actually dozens)with a macro(s) using Autofiltered
data of unknown range.
This code works fine to adjust the range, except the values in AA are result
of IF formula with "" as one option and if the bottom row of the column AA
has the "", the whole series does not chart. If the result of the bottom
line of AA is a number, even zero, all is well.

Sheets("INquiry").Select
lastrow = Cells(Rows.Count, "AA").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AC9:AC" & lastrow)

I don't want to replace the "" with a zero because I use the formula
results for other things besides just drawing a chart and don't want zeros
there. I suppose I could use helper columns and substitute the zero for the
"" there and use them to draw the charts, but that seems kinda "brute
force".....even for ME <G.

Oh yeah, I've also even tried changing the "Cells(Rows.Count, "AA")" to an
adjacent column that does have a number in that bottom row, but no
joy.....it's still the same.

Please if anyone has an idea about how to modify the code, so that the
series would chart even tho the bottom row is a "", I would muchly
appreciate. It's ok if the "" plots as a 0, all the others within the series
do that and it's fine.....it's just the bottom one that's causing me grief.

TIA
Vaya con Dios,
Chuck, CABGx3