Vba countif visible cells

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Sep 20, 2008

• #2

Hi,

Try,

Code:

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

or store the results in a variable.

 

Upvote 0

S

Shind

Board Regular

Joined

Feb 12, 2006

Messages

191

• Sep 20, 2008

• #3

Hi, thats still doesn't seem to work.

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Sep 20, 2008

• #4

Can we see your whole code?

 

Upvote 0

S

Shind

Board Regular

Joined

Feb 12, 2006

Messages

191

• Sep 20, 2008

• #5

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"

 

Upvote 0

S

Shind

Board Regular

Joined

Feb 12, 2006

Messages

191

• Sep 20, 2008

• #6

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")

 

Upvote 0

S

Shind

Board Regular

Joined

Feb 12, 2006

Messages

191

• Sep 20, 2008

• #7

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

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Sep 26, 2008

• #8

Hi,

Sory for the late reply.

Try,

Code:

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

 

Upvote 0