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.

 

[Joe4]

I am unable to reproduce that behavior you mention.

Following the same logic you used (which I also use all the time), I created a short macro which uses that methodology to return the last row in column N.

    myLastRow = Cells(Rows.Count, 14).End(xlUp).Row
    MsgBox myLastRow

I populated the first 10 rows of column N and ran it, and it returned 10.
I deleted the entries in cell N9 and N10, and re-ran the code. It correctly returned the value of 8.

How exactly are you deleting the values in column N (deleting rows, cells, or just typing over them)?
What is it that you are trying to do with this loop (might help to see the rest of the code)?

 

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

Hi Joe4,

I should have posted the line ...

For Each Total In Range(Cells(8, 5), Cells(Rows.Count, 5).End(xlUp))

It works if I delete the values at the end of the column but if I highlight a dozen or so say in the middle and press the delete button and re-run the code it still counts them as having a number in them.
Thanks.

 

[Joe4]

It works if I delete the values at the end of the column but if I highlight a dozen or so say in the middle and press the delete button and re-run the code it still counts them as having a number in them.

Where is your formula doing any counting?
It's not, it is just looping through a range that starts on E8 and goes down to the last populated cell in column E.

If you just want to them, here is one way (MsgBox returned with count):

    Dim myRange As Range
    Set myRange = Range(Cells(8, 5), Cells(Rows.Count, 5).End(xlUp))
    MsgBox Application.WorksheetFunction.CountA(myRange)

 

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

Thanks for the reply Joe4,

Here is the full code I am using ...

Option Explicit
Option Base 1

Sub Multiple_Combination_Checker_PAB()
Dim Start As Double
Start = Timer
Dim Bonus As Long
Dim CombinationDrawn As Range
Dim CombinationToCheck As Range
Dim Matched() As Long
Dim NonBonus As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Sheets("Sheet1").Select
Range("U8:AB2008").ClearContents

For Each CombinationToCheck In Range(Cells(8, 14), Cells(Rows.Count, 14).End(xlUp))
    Erase Matched
    ReDim Matched(0 To 7)
    For Each CombinationDrawn In Range(Cells(8, 5), Cells(Rows.Count, 5).End(xlUp))
        NonBonus = Evaluate("Sum(Countif(" & CombinationToCheck.Resize(1, 6).Address & _
            "," & CombinationDrawn.Resize(1, 6).Address & "))")
        Bonus = Evaluate("Countif(" & CombinationToCheck.Resize(1, 6).Address & _
            "," & CombinationDrawn.Offset(0, 6).Address & ")")
        If NonBonus = 6 Then
            Matched(7) = Matched(7) + 1
        ElseIf NonBonus = 5 And Bonus = 1 Then
            Matched(6) = Matched(6) + 1
        Else
            Matched(NonBonus) = Matched(NonBonus) + 1
        End If
    Next
    CombinationToCheck.Offset(0, 7).Resize(1, 8).Value = Matched
Next

Range("A1").Value = Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss")

Range("AE16").Select
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks.

 

[Joe4]

Did you see my comments in the previous post?
What exactly is your code supposed to be doing?

If in the process of looping through your data you are trying to do some counts, and you have blanks in the middle of your data, if you cannot make use of the formula I posted in my previous post, then simply add an IF statment at the top of the loop that checks for an entry in that cell, and if it doesn't find one, skips those steps that are adding values to your running counts.

 

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

Hi Joe4,

I have tried to add an IF statement and several other ideas as suggested but unfortunately I am unable to get it to work.

 

[Joe4]

Can yoyu just describe what it is that your code is doing (or supposed to be doing)?

See a small data set may also be helpful, so we can follow your code and see what you are trying to do.

 

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

It checks several 6 number combinations against a another list of 6 number combinations to see how many times they have matched a certain number of times.
Thanks.

 

[Joe4]

OK, this seems to be quite a bit more complex than your original question let on.

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.

It seems that your questions may have changed a bit. Are you actually having problem counting the number of matches, or counting the number of non-blank cells?