Count Non Blank Cells

[S.H.A.D.O.]

Good evening,

I have a program that uses the line of code ...

For Each TotalToCheck In Range(Cells(8, 14), Cells(Rows.Count, 14).End(xlUp))

... which works great.
However, when I highlight and delete "x" number of cells and re-run the program it does not recognise the fact that there are less cells with numbers in and gives me the wrong answer.
I have tried to adapt this particular line using DCOUNT & COUNTA etc but without any success.
Has anyone got any ideas please?
Thanks in advance.

 

[S.H.A.D.O.]

Hi Joe4,

I think I know why it is counting blank cells within the data as opposed to only cells with data in.
I am using ...

Cells(Rows.Count, 5).End(xlUp))

so it counts from E8:J last cell used and ignores if there is data or not from E8:J last cell.
Kind regards.

 

[Joe4]

I think I know why it is counting blank cells within the data as opposed to only cells with data in.
I am using ...

Cells(Rows.Count, 5).End(xlUp))

so it counts from E8:J last cell used and ignores if there is data or not from E8:J last cell.

That code, in and of itself, is not counting anything. It is simply finding the last cell in column 5 that has data in it. Take a look at the code I posted in post #4 to count the number of cells in this range that have data in them:
http://www.mrexcel.com/forum/showpost.php?p=2862395&postcount=4

 

[S.H.A.D.O.]

Thanks Joe4,

When I incorporate your code it does indeed tell me the correct number of cells with data in and when I blank some of the cells in the range and run it again, it gives me the correct reduced count.
Regards.

 

[Joe4]

Great! Glad to hear it worked out for you.

 

[S.H.A.D.O.]

Hi Joe4,

Alyhough this part of it works it now proves that the error is somewhere in the bones of the code itself that it is using the blank cells as a combination and adding them to the totals which is wrong.
Thanks in advance..

 

[Joe4]

I am afraid that arays are not my strong point.

However, which version of Excel are you using? If using 2007 or higher, you may want to use COUNTIFS instead of COUNTIF, where you can have more than one condition (and you can check for blank options).

If you are using an older version of Excel, you may want to look at using SUMPRODUCT instead, in which you can have multiple criteria and do summing or counting.

 

[S.H.A.D.O.]

Thanks Joe4,

I am using Excel 2007.
I have tried to put in the extra criteria to only count if the cell or combination is greater than blank but without any success.
The SUMPRODUCT still returns a count on the blank data.
Thanks in advance.

 

[Joe4]

Can you post the COUNTIFS function you tried?