Dynamic Range

[Brian]

I am trying to create a macro and am stuck trying to
figure out how to determine my last column. In cell E3 I
am using the formula =COUNTIF(E4:??,0). Can I create a
dynamic range going across columns? In other words, on
some sheets, it may be =COUNTIF(E4:S4,0) and other times
it may be =COUNTIF(E4:AA4,0). tia

 

[Peo Sjoblom]

You can use something like

=COUNTIF(OFFSET($E$4,,,1,COUNTA($E$4:$IV$4)),0)

or if you are using it on sheet3 , do insert>name>define and call the offset
part "MyRange" (w/o quotes)
and in the refers to box put

=OFFSET(Sheet3!$E$4,,,1,COUNTA(Sheet3!$E$4:$IV$4))

then use it like

=COUNTIF(MyRange,0)

 

[Peo Sjoblom]

I might add that you might want to check the range since if it is empty it
will return an error

=IF(COUNTBLANK(E4:IV4)=252,"",COUNTIF(MyRange,0))

 

[Brian]

Works great, thanks. can you tell me why I get a screen
that says 'Update Values'?? Is there something I can do
so this does not show up?

 

[Peo Sjoblom]

That is the drawback with offset, it is volatile so even if you don't do any
change it will always
ask if you want to save the workbook when you close it.. It might be worth
that though..

 

[Aladin Akyurek]

Also...

If the ranges the CountIf formula is applied to houses numeric data (number,
date, or time), try...

=COUNTIF(E4:INDEX(4:4,MATCH(BigNum,4:4)),0)

where BigNum is a defined name referring to 9.99999999999999E+307.