excelvba

Manipulation within filtered range


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.


Solution

  • 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