I've been thinking about getting the data in the 4th filtered range and what works for me is to loop through all visible cells and exit for when the counter hits 4.
Sub VBAArray2()
Dim randomRng As Range, counterGuess As Integer
With Sheets(1)
.Range("A1").AutoFilter 3, "A<B"
For Each randomRng In .Range("A2:A11").SpecialCells(xlCellTypeVisible)
If randomRng < randomRng.Offset(0, 1) Then
counterGuess = counterGuess + 1
End If
If counterGuess = 4 Then
Exit For
End If
Next
MsgBox randomRng.Value
End With
End Sub
May I know if there's more straightforward alternative ? specialcells(xlcelltypevisible).Areas(4)
doesn't always work since the 4th filter range might be different areas each time.
Alternative is to loop through areas
Option Explicit
Sub VBAArray2()
Const NUM = 4
Dim rng As Range, lastrow As Long
Dim i As Long, m As Long, n As Long
' set filter
With Sheets(1)
lastrow = .Cells(.Rows.Count, "A").Rows(xlUp).Row
.Range("A1").AutoFilter 3, "A<B"
Set rng = .Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible)
End With
' check enough rows
If rng.Cells.Count < NUM Then
MsgBox "Row count < " & NUM, vbCritical
Exit Sub
End If
' iterate areas
Do
m = n
i = i + 1
n = n + rng.Areas(i).Rows.Count
Loop While n < NUM
' result
MsgBox rng.Areas(i).Cells(NUM - m)
End Sub