Vba countif visible cells

[Shind]

Hi,
Have the below code which keeps come back "Expected end of statement'. I dont know where its going wrong, can you help?

Application.WorksheetFunction.CountIf(Range("BQ2:BQ" & Range("BQ65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)),"delete")

Thanks

 

[Krishnakumar]

Hi,

Try,

MsgBox Application.WorksheetFunction.CountIf(Range("BQ2:BQ" & Range("BQ65536"). _
    End(xlUp).Row).SpecialCells(xlCellTypeVisible), "delete")

or store the results in a variable.

 

[Shind]

Hi, thats still doesn't seem to work.

 

[Krishnakumar]

Can we see your whole code?

 

[Shind]

Hi sorry for the late response.

Heres the code im using.

Just filtering a column.

Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">30", Operator:=xlAnd

the as above my problem code. just want to know how many equals "delete"

 

[Shind]

these are the three codes iv'e tried. Nonhe of which work.

Code1

MsgBox Application.WorksheetFunction.CountIf(Range("BQ2:BQ" & Range("BQ65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible), "Delete")

Code 2

MsgBox Application.WorksheetFunction.CountIf(Columns("BQ").SpecialCells(xlCellTypeVisible), "Delete")

Code 3
MsgBox Application.WorksheetFunction.CountIf(Range("BQ2:BQ200").SpecialCells(xlCellTypeVisible), "Delete")

 

[Shind]

Figured something out Krishna, the code you provided does work BUT.... not when i have the filters on.
is there any way around this

 

[Krishnakumar]

Hi,

Sory for the late reply.

Try,

Function COUNTIFVISIBLE(r As Range, Crit) As Long
Dim s   As String, f As String, sRow As Long
s = r.Address: sRow = r.Row
If Not IsNumeric(Crit) Then Crit = """" & Crit & """"
f = "=sumproduct(subtotal(3,offset(" & s & ",row(" & s & ")-" & _
        sRow & ",,1)),--(" & s & "=" & Crit & "))"
COUNTIFVISIBLE = Evaluate(f)
End Function
Sub test()
MsgBox COUNTIFVISIBLE(Range("BQ2:BQ200"),"Delete")
End Sub

HTH