How to find the Count of Records in List in atleast one-null scenario?

[prabhafriend]

How to find the Count of Records in Excel List when All of the Listcolumns atleast have a null in it?

[Bob Phillips]

I understand all of the words but not the sentence.

[Aussiebear]

I think prabhafriend is trying to count all cells used in a range

[prabhafriend]

Ok. Lets Explain this way. We are making a list for the Range A1:C6. Obviously A1:C1 is the HeaderRow Range. Let's Add the TotalRowRange of Sum in the Listcolumn C. Summing is not a problem, We will exactly get the sum. Now I want to Count the Records (Count of Databodyrange.rows). I know I can get it by adding a count in the Totalrowrange in any of the column in that list. The problem is 'Count' skips the null values in the column. I don't have any primary keys(columns which don't have null in that). What do to in this scenario? How to count the no.of records?

[Bob Phillips]

Sum will not return a null, even if its source cells are null, so COUNT includes it.

[prabhafriend]

Friend. Count is Ignoring Null Values. For If my ListColumn(1) is having values 1,(null),2,3,5. Count of that returns only 4. But I want the list of records. i.e How?

[Bob Phillips]

I didn't say Count, I said Sum. You said ... Let's Add the TotalRowRange of Sum in the Listcolumn C... so I am saying just count those.

[prabhafriend]

I tried counting those. the problem is when there is a null value comes in that column, it skips that. That is the actuall problem.

[Bob Phillips]

We are going round in circles here, but how can there be a null value if it is a sum of the rest of the row?

[prabhafriend]

I want to count the records in ListcolumnA. ListColumnA has a Null value in one of its cell. The Count in the totalrow counts only the filled cells but I want the total count including the non-filled cells. What to do?

[mdmackillop]

=COUNT(H1:H9)+COUNTBLANK(H1:H9)

[Bob Phillips]

=COUNT(H1:H9)+COUNTBLANK(H1:H9)

How about just

=9

[prabhafriend]

xld. Getting that 9 is the problem. Activesheet.Listobjects(1).Databodyrange.Columns(1).cells.count Gives the Count of Cells in the Entire List Range. What is the Easiest Way to get that 9.