Dear all,
I have this code but the count is wrong if my autofilter is filtered with data.
I also want to do following:
1. On multiple excel file in same folder (this is optional if unable to do this). The reason i want this is because i have many workbooks.
2. Autofilter with mutiple criteria in column D and J. *Note that some criteria may not exist in the column.
3. Count visible cells based on column C
4. Copy each count obtain from the closed files into one single workbook.
Code
- Sub filtered_row_count()
- Dim var1, var2, var3, CountRow As Long
- Dim lngCount As Long
- 'Set Workbk = ThisWorkbook
- Sheets("Sheet1").Select
- Sheets("Sheet1").AutoFilterMode = False ' remove autofilter if is there.
- Selection.AutoFilter Field:=5, Criteria1:="Projector", Operator:=xlOr, Criteria2:="Print" ' Print may not exist
- var1 = Range("C:C").SpecialCells(xlCellTypeVisible).Count
- var2 = Range("C65535").End(xlDown).Row
- var3 = Range("C" & var2).End(xlUp).Row
- CountRow = (var3 + K) - var2
- 'lngCount = Range("C:C").Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Count - 1
- 'If Range("C1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then Exit Sub
- MsgBox CountRow
- End Sub
Hope to receive your advice, thanks!