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.