Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Number of Discrepancies between 2 rows | Excel Worksheet Functions | |||
Counting number of rows | New Users to Excel | |||
Counting number of rows | New Users to Excel | |||
counting the number of equal rows | New Users to Excel | |||
Counting the number of rows | Excel Worksheet Functions |