filtering

teoant · Jan 30th 2003

I'm using auto-filter in my applications in order to filter my data(a name for example).my problem is that i can't figure out a way to create a macro that will help me "pick" the name and copy it to another cell.i'm interested in copying just the criteria and not the whole selection.

btadams · Jan 30th 2003

Need a better explanation. Do you want an inputbox so a user can enter a name and the database will filter on that name and copy some stuff to another worksheet?

Hans Pottel · Jan 31st 2003

If you want to detect which columns contain a filter and the criteria for the filter, then you can apply the following code (it assumes you have a database with a filter and some criteria in the worksheet 'Sheet1':

Code

Sub Test()
    Dim filterArray()
    Dim f As Integer
    Dim currentFiltRange As String
    Dim w As Worksheet
    Set w = ActiveWorkbook.Sheets("Sheet1")
    With w.AutoFilter
    currentFiltRange = .Range.Address
    MsgBox currentFiltRange
    
    With .Filters
        ReDim filterArray(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filterArray(f, 1) = .Criteria1
                    If .Operator Then
                        If .Operator = 1 Then
                           filterArray(f, 2) = "AND"
                        ElseIf .Operator = 2 Then
                           filterArray(f, 2) = "OR"
                        End If
                        filterArray(f, 3) = .Criteria2
                    End If
                    MsgBox "Column " & f
                    MsgBox "Criterium 1 = " & filterArray(f, 1)
                    MsgBox "Operator = " & filterArray(f, 2)
                    MsgBox "Criterium 2 = " & filterArray(f, 3)
                End If
           End With
        Next f
    End With
    End With
End Sub

Display More

Hans

Hans Pottel · Feb 1st 2003

I don't know how to trigger an event by just changing the filter criterion. In case you change something on your sheet or in case you select another cell, you can use the workbook sheetchange and workbook sheetselectionchange events to put the "criterion" in the cell you want. Like this: In the ThisWorkbook module, put the following code:

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Call UpdateCell15
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Call UpdateCell15
End Sub

Then add a general module to the VBAproject and add the following code

Code

Sub UpdateCell15()
    Dim f As Integer
    Dim w As Worksheet
    Set w = ActiveSheet
    With w.AutoFilter
    With .Filters
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    w.Cells(1, 5).Value = Right(.Criteria1, Len(.Criteria1) - 1)
                End If
           End With
        Next f
    End With
    End With
End Sub

Display More

It would be better if cell(1, 5) was automatically filled with the name in your selection, but I can't figure out how this can be done.

Hans

filtering

Participate now!

Share