countif

[Brian C]

I am counting the rows in a selected column using the following code which is
working fine. However, I would like to modify this to count the rows where
the value <> 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count > 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count > 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

 

[excelent]

Sub RowCount()
Dim x, Xcount
For Each x In Selection
If x.Value <> 0 Then Xcount = Xcount + 1
Next
MsgBox ("Numbers of rows <> 0 : ") & Xcount
End Sub

 

[Don Guillett]

might be easier to just use

Sub countifnotzero()
MsgBox Application.CountIf(Columns(5), "<>0")
'or
'MsgBox Application.CountIf(Selection, "<>0")
End Sub

 

[Bob Phillips]

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<>"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Brian C]

Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<>"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<>")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<>0")

I've tried modifying the suggested code by adding the 0 but have had no
success.

 

[Bob Phillips]

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<>0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Brian C]

That's it

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<>0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Brian C]

I need to modify this formula depending on the value in the cell immediately
above the active cell as follows:

if value <> 0 then "=COUNTIF(" & rng.Address & ",""<>0"")-1"
else
if value = 0 then "=COUNTIF(" & rng.Address & ",""<>0"")"

I am having problems with the syntax in referring to another cell.

Thanks

 

[Bob Phillips]

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<>0"")" & _
IIf(rng1.Offset(-1, 0).Value <> 0, "-1", "")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)