Excel VBA – Select Filtered data using Autofilter in VBA via a Temporary Sheet

Autofilter commend can quickly filter out the information satisfied the criteria. Is there some way to select the data that are found after an autofilter using VBA?

Here is one method I use.

Step 1, Add a temporary sheet

Step 2, Copy and paste the filtered result to the temporary sheet
Step 3, Manipulate the data
Step 4, Delete the temporary sheet
 

The key commend I used here is .SpecialCells(xlCellTypeVisible), which allow to copy only the filtered results.

Sub main()

    Dim FiltResult As String

    Dim Filtersheet As Worksheet

    Dim FiltRange As Range

    Dim FiltField As Integer

    Dim FiltCrit As Variant

    Set Filtersheet = sheets("Sheet1")

    '---The targeted range object

    Set FiltRange = Filtersheet.Range("C6:F7000")

    FiltField = 1

    FiltCrit = 900

    FiltResult = Copyfilter(Filtersheet, FiltRange, FiltField, FiltCrit)

    '''''

    ''''Codes related with the filtered result

    ''''

    '------Delete the temporary sheet

    sheets(FiltResult).Delete

End Sub

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

        Dim Sh1 As Worksheet

        '------Add a temporary sheet to save the filtered result

        Set Sh1 = Worksheets.Add

        '------Transfer the name of the temporary Sheet to the main program

        Copyfilter = Sh1.Name

        Sht.Select

        With Sht

            'Remove the previous filter if any      

            Rng.AutoFilter 

            Rng.AutoFilter Field:=FField, Criteria1:=Crit

            '----Copy the data to temporary sheet for future use

            Rng.SpecialCells(xlCellTypeVisible).Copy Destination:=Sh1.Range("A1")

            'Remove the filter

            Rng.AutoFilter Field:=FField

        End With

End Function

© Copyright Exceltipsandkeys All Rights Reserved.