MsgBox with count

[David0079]

Hi,
I've created this MsgBox to show the results of a filter. Sadly my knowledge will only take me as far as counting all the rows up from the bottom including the blank ones! I have a header in row 3 so would like to count all only cells with information in them from cell A4 down to the last, could anyone help tweak my code to do this. Tank you all!

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
MsgBox rng.Columns(1). _
   SpecialCells(xlCellTypeVisible).Count - 1 _
   & " of " & Worksheets("jobs").Cells(Rows.Count, 1).End(xlUp).Row + 1 & " jobs are still unfinished and have passed their deadline!", vbCritical, "Uh oooh"

 

[Norie]

David

Couldn't this be done with a CountIf?

Which could be accessed in code via Application.WorksheetFunction.

 

[David0079]

hmmm How do I use that function for this task?

 

[David0079]

Dim myRange As Range
Set myRange = Worksheets("Actions").Range("A4:A500")
answer = Application.WorksheetFunction.CountIf(CONTAINS TEXT?)
MsgBox & "of" answer


????

 

[Paddy1979]

Like this david

X = Application.WorksheetFunction.CountA(Range("A:A")) - 1
Z = Application.WorksheetFunction.CountA(Range("jobs!A:A")) + 1

 

[Norie]

david

The syntax would depend on what you are actually trying to count.

And we don't know what that is because we haven't seen any data and don't know what/how you are filtering.

 

[David0079]

Yessss sorted! Thanks VERY much fellas, used this ....

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
X = Application.WorksheetFunction.CountA(Range("A1:A500"))
MsgBox rng.Columns(1). _
SpecialCells(xlCellTypeVisible).Count - 1 & " of " & X & " Jobs are still unfinished but have passed their deadline!", vbCritical, "Uh oooh"

You guys are fantastic!