Fast Filter

[adam]

Hi,
Below attached is the VB a code that I’m using to fast find values in an excel sheet.

When the search parameter is written below the “Enter your search parameter below this row” the row containing the search parameter is filtered.
But when I update the list with new customer information the cells are colored & when the tab button is pressed to move to the next column it doesn’t move at once, instead I have to press twice.

I want only the search area color to change when a parameter is entered. And also to prevent this two time tabbing thing to move to the next column.
How this problem could be solved?

Regrads
Adam

[HansV]

You have to tab twice because you explicitly tell Excel to stay in the cell that you have changed. The code contains a line

Range(Target.Address).Activate

To avoid having to tab twice, remove or comment out this line.

[adam]

Why does the sheet get filtered with the value enter in the row 9 with the appropriate text column?

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Look at the code behind the worksheet. There's a Worksheet_Change event procedure that sets or clears AutoFilter.

[adam]

Thanks for the reply. But if I may ask are you referring the to the following lines?

Code: Select all

If Cells(rownum, colnum).Value = "" Then ' No filter choice
             mylist(tblname).Range.AutoFilter Field:=colnum
             GoTo cleanup
        ElseIf caret Then
            mylist(tblname).Range.AutoFilter Field:=colnum, _
            Criteria1:=crit1, Operator:=optype, Criteria2:=crit2
            GoTo cleanup
        Else
            mylist(tblname).Range.AutoFilter Field:=colnum, _
            Criteria1:=crit1
            GoTo cleanup

[HansV]

Yes.

[adam]

I've tried by changing the code as

Code: Select all

If Cells(rownum, colnum).Value = "" Then ' No filter choice
             mylist(tblname).Range.AutoFilter Field:=colnum
             GoTo cleanup
        ElseIf caret Then
            mylist(tblname).Range.AutoFilter Field:=colnum, _
            Criteria3:=crit1, Operator:=optype, Criteria2:=crit2
            GoTo cleanup
        Else
            mylist(tblname).Range.AutoFilter Field:=colnum, _
            Criteria3:=crit1
            GoTo cleanup

But the sheet still does not get filtered. Why is this?

[adam]

Code: Select all

     Set mylist = ActiveSheet.ListObjects
     If mylist.Count Then ' A List or Table Object is used
            tblname = mylist(1).Name
        
        If Cells(rownum, colnum).Value = "" Then ' No filter choice
             mylist(tblname).Range.AutoFilter Field:=3
             GoTo cleanup
        ElseIf caret Then
            mylist(tblname).Range.AutoFilter Field:=3, _
            Criteria1:=crit1, Operator:=optype, Criteria2:=crit2
            GoTo cleanup
        Else
            mylist(tblname).Range.AutoFilter Field:=3, _
            Criteria1:=crit1
            GoTo cleanup
    End If

With the above modification the sheet gets filtered when the search text is written only in the column E and it does not get filtered when the search text is written.

[adam]

Sorry I missed a word in the above post. It would be as with the above modification the sheet gets filtered when the search text is written only in the column E and it does not get filtered when the search text is written in any other column.

Any help on this would be kindly appreciated.