【VBA】Get filtered list

VBA Code

As an example, get filtered list “sales”, Output to “immediate window”.

Option Explicit

Sub sampleProc()
    
    Dim targetSheet As Worksheet
    Dim startRange As Range
    Dim endRow As Double
    Dim endRange As Range
    Dim targetRange As Range
    Dim arrFilteredList As Variant
    Dim item As Variant
    
    'Get the target sheet with the list
    Set targetSheet = Worksheets("sample")

    'Get the start cell of the list
    Set startRange = targetSheet.Range("B3")
    'Get the last row of the list
    endRow = startRange.End(xlDown).Row
    'Get the last cell of the list
    Set endRange = targetSheet.Cells(endRow, startRange.Column)
    
    'Get the cells of the list
    Set targetRange = targetSheet.Range(startRange, endRange)
    
    'Get "filtered list"
    arrFilteredList = WorksheetFunction.Filter(targetRange, Evaluate(targetRange.Offset(0, 0).Address & ">=300"))
    
    For Each item In arrFilteredList
        'Output to "immediate window"
        Debug.Print item
    Next
    
End Sub

Result

You got “filtered list“！

FYI No.1

Also, you can get “sorted list“！

For more information, see the following article:

FYI No.2

Also, you can get “unique list“！

For more information, see the following article:

FYI No.3

For more information about following, see the following official online manual:

●FILTER Function