Counting number of rows that are not N/A
 

I have the following

RowCount = Cells(Rows.Count, "A").End(xlUp).Row

However, column A has several entries of #N/A with the function NA().

I need to count the number of entries in column A that are not NA and the
number of entries that ARE NA. How would I accomplish that?

Thanks

Counting number of rows that are not N/A
 

You could just count the number of entries in column A with the
=NA()
formula:


Sub gsnu()
Dim r As Range, r2 As Range
RowCount = Cells(Rows.Count, "A").End(xlUp).Row
Set r2 = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each r In r2
If r.Formula = "=NA()" Then
i = i + 1
End If
Next
MsgBox (i)
End Sub

so RowCount is the total
i is the number of #N/As
RowCount-i is the number without #N/A
--
Gary's Student


"Barb Reinhardt" wrote:

I have the following

RowCount = Cells(Rows.Count, "A").End(xlUp).Row

However, column A has several entries of #N/A with the function NA().

I need to count the number of entries in column A that are not NA and the
number of entries that ARE NA. How would I accomplish that?

Thanks

Counting number of rows that are not N/A
 

looping is always an option, but faster is

numNA = Application.Countif(Range("A:A"),"#N/A")
nonNA = lastrow - numNA

If the only error value is NA

set rng = Columns(1).SpecialCells(xlFormulas,xlErrors)
numNa = rng.count
nonNA = lastrow = rng.count

--
Regards,
Tom Ogilvy

"Gary''s Student" wrote:

You could just count the number of entries in column A with the
=NA()
formula:


Sub gsnu()
Dim r As Range, r2 As Range
RowCount = Cells(Rows.Count, "A").End(xlUp).Row
Set r2 = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each r In r2
If r.Formula = "=NA()" Then
i = i + 1
End If
Next
MsgBox (i)
End Sub

so RowCount is the total
i is the number of #N/As
RowCount-i is the number without #N/A
--
Gary's Student


"Barb Reinhardt" wrote:

I have the following

RowCount = Cells(Rows.Count, "A").End(xlUp).Row

However, column A has several entries of #N/A with the function NA().

I need to count the number of entries in column A that are not NA and the
number of entries that ARE NA. How would I accomplish that?

Thanks