How to count hidden/filtered out rows that has content in them using excel VBA?

Question

The code below only shows the count of the visible rows, however, when the rows are filtered prior to running the code, it won't detect the hidden rows.

Total_rows_Pick = Workbooks("Job Production Monitoring.xlsm").Worksheets("Pick-ups").Range("B" & Rows.count).End(xlUp).Row

What should I do to get the equivalent code which includes detecting even hidden/filtered out rows that has text written in them?

Example of my filtered worksheet is (notice row 2 is filtered out):

After running the line of code above,the locals window only count the headers, but not row 2.

The screenshot of the variable as shown in the locals window:

Added a simple solution looping through a datafield array of the used range. Additionally, I show an alternative based on @Jon-Crowell's interesting approach. — T.M., Apr 22 '18 at 18:53

score 1 · Answer 1 · answered Apr 22 '18 at 18:29

Loop through datafield array in used range

The solid End(xlUp) method fails with hidden rows, as there is nothing to move in the visible range. Therefore I'd simply try to loop through the used range starting in the last row of column B and check for values.

Hint: It's good practice to use a variant datafield Array (called v in the example code) as looping through ranges via VBA is slow.:

Example Code

Sub FindLastRow()
Dim i As Long, Total_rows_Pick As Long
Dim ws As Worksheet, v As Variant
Set ws = ThisWorkbook.Worksheets("Pick-ups")
v = ws.Range("B1:B" & ws.UsedRange.Rows.Count)  ' write data into 1-based 2-dim datafield array
For i = UBound(v) To 2 Step -1                  ' start search in last row of used range
    If Len(v(i, 1) & "") <> 0 Then              ' take first value
       Exit For
    End If
Next i
total_rows_pic = i                              ' last row with value
MsgBox Total_rows_Pick
End Sub

Alternative solution via temporary sheet

Jon Crowell showed an alternative solution by copying the original data sheet to a temporary sheet; unhiding all rows there allows to use the original method to find the last row:

Sub FindLastRow2()
' Modified source: https://stackoverflow.com/questions/14200392/finding-the-last-row-of-an-excel-spreadsheet-when-the-last-row-is-hidden
' Thx:  Jon Crowell
  Dim Total_rows_Pick As Long, ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Pick-ups")
' copy original data to temporary sheet and unhide all rows
  ws.Copy Before:=ws
  With ActiveSheet                  ' << temporary sheet only
    ' [1]turn off autofiltering
      If .AutoFilterMode Then .AutoFilterMode = False
    ' [2] unhide all rows
      .Columns("B:B").EntireRow.Hidden = False
    ' [3] get the last row there
      Total_rows_Pick = .Range("B" & .Rows.Count).End(xlUp).Row
    ' [4] delete the temporary sheet
      Application.DisplayAlerts = False
      .Delete
      Application.DisplayAlerts = True
  End With
  MsgBox Total_rows_Pick
End Sub

@Pherdindy, could you give my two approaches in my answer a try? I'd appreciate if you marked it as accepted in case you found a helpful solution. — T.M., Aug 16 '18 at 19:37

How to count hidden/filtered out rows that has content in them using excel VBA?

1 Answers1