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
Fast Filter
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Fast Filter
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fast Filter
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.
Range(Target.Address).Activate
To avoid having to tab twice, remove or comment out this line.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Fast Filter
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.
Any help on this would be kindly appreciated.
Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fast Filter
Look at the code behind the worksheet. There's a Worksheet_Change event procedure that sets or clears AutoFilter.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Fast Filter
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
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Fast Filter
I've tried by changing the code as
But the sheet still does not get filtered. Why is this?
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
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Fast Filter
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
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Fast Filter
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.
Any help on this would be kindly appreciated.
Best Regards,
Adam
Adam