0

Apologies if the answer is obvious, only been using VBA for a week. Want to filter the cells in a separate sheet, then count the blanks left in the filtered list. I have got the code basics from existing answers, so didn't concoct myself, and it almost works, but thought I would ask after staring at this for 3 hours and trying different stuff.

The code produces a result, it's just 1 more than is correct each time, so there are two blanks in the filtered list but the ouput is 3. Have tried all sorts of adjustments to the list, but it's always 1 more than expected. Will try and attach pictures of the list I am filtering.

I also have code to dynamically find the end of the range and apply the filter. I will add an 'unfilter' code line too once I get this bit working.

Sub BlankCheck()
    ' Find last free row
    Dim lRow As Long
        lRow = Worksheets(2).Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    
    ' Filter
    Worksheets(2).UsedRange.AutoFilter Field:=1, Criteria1:="test"
    
    ' Count visible blanks
    Worksheets(1).Cells(1, 1) = Range(Worksheets(2).Cells(1, 2), Worksheets(2).Cells(lRow, 2)). _
    SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count
    
End Sub

Thanks in advance for any help.

image of unfiltered list

1 Answer 1

0

To let you know why this returns 3, is that you currently are looking for visible cells that have content in the B column. So it includes the header AND the two cells that have content after the filter is applied.


If your goal is to return the number of empty cells with the criteria in column A, you could also use:

Sub Test()

With ThisWorkbook.Worksheets("Sheet1")
    Debug.Print Application.WorksheetFunction.CountIfs(.Range("A:A"), "test", .Range("B:B"), "")
End With

End Sub

On your sample data, this returns 2.

4
  • So I kinda get what you're saying, but why does it include the header when the header is not blank and I am counting blanks (although I must admit, I don't really know how I am counting blanks, as I just copied the code from another site. Anyway thanks and I will implement your suggestion.
    – user856111
    Aug 16, 2019 at 11:57
  • @redstamp, don't know where you got the code from but it simply isn't counting empty cells. It's counting visible cells with constant values in them from the very first row.
    – JvdV
    Aug 16, 2019 at 12:34
  • @redstamp, also. If this has been helpfull or has answered your question. Please consider return the favour and upvote/accept as answer.
    – JvdV
    Aug 16, 2019 at 14:55
  • yes very helpful, and done
    – user856111
    Aug 17, 2019 at 0:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .