CODEWITHSUNDEEP
vbaexcelfilter
SFro
SFro

Reputation: 131

Filtering list in Excel VBA without autofilter

I would like to be able to filter a list based on the criteria selected in a list box. This is my current approach.

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheet1
    .AutoFilterMode = False
    .Range("A1:D1").AutoFilter
    .Range("A1:D1").AutoFilter field:=2, Criteria1:=Range("H9").Value
End With
End Sub

My issue with the autofilter is that it actually hides the rows that do not fit the criteria from the list box. What I would like is for the individuals rows of data to become hidden, but by somehow being removed from the actual list, and the values below that were not filtered taking its place.

For example:

Name    Age
john    20
john1   28
john2   30
john3   20
john4   19

What I envision happening, is that if age 20 was selected from the drop down, john3 would would move up to the position that john1 was in, and the remaining rows are removed (but not simply hidden).

My goal is to create a large list of data that will "roll back" as more criteria are selected from list boxes.

Thank you!

Upvotes: 1

Views: 3949

Answers (2)

nwhaught
nwhaught

Reputation: 1592

I use the code below all the time for "cleaning up" a large filtered list and emailing the appropriate subset to different people. It deletes all hidden rows, including those hidden by the autofilter. Note that you can't get these back! If you want to be able to "reset" your list, I'd save a copy of the list on another sheet, and provide some way for it to be recopied into your active sheet when the user needs to reset the data. 

Sub CutFromFilter()
    Dim oRow As Range, rng As Range
    Dim myRows As Range
    With ActiveSheet
        Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
        If myRows Is Nothing Then Exit Sub
    End With

    For Each oRow In myRows.Columns(1).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next

    If Not rng Is Nothing Then rng.EntireRow.Delete
    ActiveSheet.Range("A1").Select
End Sub

Upvotes: 1

Eric K.
Eric K.

Reputation: 834

If you already have logic can done the autofilter,

You can using below code to add those visible items into your list

nRow will be the record that visible, using the number to control which column value you want to add into list box

Dim Rng, nRow As Range
Dim N As Single

Set Rng = ActiveSheet.AutoFilter.Range

    For N = 2 To Rng.Rows.Count
        Set nRow = Rng.Rows(N)
        If Not nRow.Hidden Then
            MsgBox nRow(1) '1 means first column of AutoFilter Range

        End If

    Next

Upvotes: 0

Related Questions