Dynamic range - ignoring formulas

Hello HuskerBronco,

You can accomplish this by creating a User Defined Function (UDF). A UDF is a macro you call like a worksheet function. This UDF will count only cells without formulas and that aren't blank.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

COUNTBLANK function counts both genuinely blank cells and cells with "formula blanks", i.e. ""

So you could use this formula for the number of cells in column A with data

=65536-COUNTBLANK(A:A)

Thanks for your solutions, guys!!
Both solutions are excellent, and both worked to perfection.

I have formulas (some with results, some not) that are all being referenced by the named range when I use,
=OFFSET(Products!$AA$1,1,0,(65536-COUNTBLANK(Products!$AA:$AA)-1),1)
=OFFSET(Products!$AA$1,1,0,COUNTA(Products!$AA:$AA)-1,1)

Pasting values to a new column doesn't seem make a difference.
1. How can I get this to work with formulas?
2. How can I implement the UDF suggestion above in conjunction with the named range formula?