Results 1 to 8 of 8

Vba countif visible cells

This is a discussion on Vba countif visible cells within the Excel Questions forums, part of the Question Forums category; Hi, Have the below code which keeps come back "Expected end of statement'. I dont know where its going wrong, ...

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    191

    Default Vba countif visible cells

    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

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Vba countif visible cells

    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.

  3. #3
    Board Regular
    Join Date
    Feb 2006
    Posts
    191

    Default Re: Vba countif visible cells

    Hi, thats still doesn't seem to work.

  4. #4

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Vba countif visible cells

    Can we see your whole code?

  5. #5
    Board Regular
    Join Date
    Feb 2006
    Posts
    191

    Default Vba countif visible cells

    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"

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    191

    Default Vba countif visible cells

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

  7. #7
    Board Regular
    Join Date
    Feb 2006
    Posts
    191

    Default Vba countif visible cells

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

  8. #8

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Vba countif visible cells

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com