Detect largest range of visible cells in a filtered range

[eran85]

I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.

 

[venkat1926]

use subtotal after filtering. it subtotals on only visible data. for max it is 104

[TABLE="width: 619"]
<tbody>[TR]
[TD]valuates hidden values[/TD]
[TD]Ignores hidden values[/TD]
[TD]Function[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]101[/TD]
[TD]AVERAGE()[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]102[/TD]
[TD]COUNT()[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]103[/TD]
[TD]COUNTA()[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]104[/TD]
[TD]MAX()[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]105[/TD]
[TD]MIN()[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]106[/TD]
[TD]PRODUCT()[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]107[/TD]
[TD]STDEV()[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]108[/TD]
[TD]STDEVP()[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]109[/TD]
[TD]SUM()[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]110[/TD]
[TD]VAR()[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]111[/TD]
[TD]VARP()[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Don't know exactly what you want to do with the range but with 200,000 rows filtered into about 50,000 different areas, this worked pretty quickly for me.

Sub FindLargestArea()
  Dim r As Range, BigRange As Range
  Dim MaxRows As Long, AreaCount As Long
  
  For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
    AreaCount = AreaCount + 1
    If r.Rows.Count > MaxRows Then
      MaxRows = r.Rows.Count
      Set BigRange = r
    End If
  Next r
  MsgBox "Large range: " & BigRange.Address(0, 0) & vbLf & "Max rows: " & MaxRows & vbLf & "Number of areas checked = " & AreaCount
End Sub

Edit: Also not sure what you would want to do if there are multiple areas with the same (max) number of rows. This code will just report the first such one.

 

[eran85]

Thanks for the answers.
Both solutions seem fine and I already implemented something similar to the second.
I wasn't aware of the "areas" property. great!