# Dynamic Range to Exclude Blank Cells with Formulas

#### Dokat

##### Active Member
I have below dynamic range formula to automatically change chart range. However i need formula to ignore blank cells with formula and exclude it from the range. Did anyone came acriss similar issue?

=(Inno!\$AU\$6:INDEX(Inno!\$AU6:\$AU\$65,COUNTIF(Inno!\$AU6:\$AU\$65,"<>"&""))

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Does your column of data have blank cells throughout the column? Are you asking for the chart data not to use the blanks within the range?
If so, I don't think your going to get what you want. A chart series has to be contiguous (continuous, uninterrupted set of data) range. If you need your non-blank cells aggregated together, we would have to go a different way.

Dynamic named ranges count the number of non-blank cells and return a row. You can have it return the last non-blank row. In your case, Countif is only counting the non-blank cells. It won't include all the data if blanks are included.

=offset(\$AU\$6,0,0,match(1e+300,\$AU\$6:\$AU\$65),1)
This would return the last row with a number in it. It would still return the full range including any blanks.

Does your column of data have blank cells throughout the column? Are you asking for the chart data not to use the blanks within the range?
If so, I don't think your going to get what you want. A chart series has to be contiguous (continuous, uninterrupted set of data) range. If you need your non-blank cells aggregated together, we would have to go a different way.

Dynamic named ranges count the number of non-blank cells and return a row. You can have it return the last non-blank row. In your case, Countif is only counting the non-blank cells. It won't include all the data if blanks are included.

=offset(\$AU\$6,0,0,match(1e+300,\$AU\$6:\$AU\$65),1)
This would return the last row with a number in it. It would still return the full range including any blanks.

Thank you

Replies
3
Views
145
Replies
1
Views
58
Replies
5
Views
118
Replies
1
Views
49
Replies
3
Views
81

1,196,485
Messages
6,015,472
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back