Dynamic Range

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

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 insertnamedefine 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)

--

Regards,

Peo Sjoblom


"Brian" wrote in message
...
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

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))

--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
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 insertnamedefine 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)

--

Regards,

Peo Sjoblom


"Brian" wrote in message
...
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

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?



-----Original Message-----
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 insertnamedefine
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)

--

Regards,

Peo Sjoblom


"Brian" wrote in
message
...
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


.

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..

--

Regards,

Peo Sjoblom


"Brian" wrote in message
...
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?



-----Original Message-----
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 insertnamedefine
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)

--

Regards,

Peo Sjoblom


"Brian" wrote in
message
...
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


.

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.

"Brian" wrote in message
...
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