Macro to filter from values in column based on active cell

[wsarij]

I need some urgent help. I need a macro which would filter items within a column based on the entry that is on the active cell (as in the cell which is active). I came up with the following using the Record - but right now it is looking up the specific name "Adam Hill" - - I would like it to look up the value that is active within the particular cell, which is also within the column being filtered.



ActiveSheet.Range("$A$8:$K$2370").AutoFilter Field:=1, Criteria1:= _
"Adam Hill"
End Sub

 

[Harry Flashman]

I need some urgent help. I need a macro which would filter items within a column based on the entry that is on the active cell (as in the cell which is active). I came up with the following using the Record - but right now it is looking up the specific name "Adam Hill" - - I would like it to look up the value that is active within the particular cell, which is also within the column being filtered.



ActiveSheet.Range("$A$8:$K$2370").AutoFilter Field:=1, Criteria1:= _
"Adam Hill"
End Sub

Try this. Right click the worksheet and choose View Code. Paste the macro in window that appears.

This will filter your data on double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Col As Long
    Col = Selection.Column
    Selection.AutoFilter Field:=Col, Criteria1:=Target.Value, Operator:=xlFilterValues
    Cancel = True
End Sub

 

[zero269]

Try this. Right click the worksheet and choose View Code. Paste the macro in window that appears.

This will filter your data on double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Col As Long
    Col = Selection.Column
    Selection.AutoFilter Field:=Col, Criteria1:=Target.Value, Operator:=xlFilterValues
    Cancel = True
End Sub

WARNING!

I just tried using this code in one of my Workbooks and it caused Excel to glitch to the point I couldn't use it.
Task Manager didn't show it hung up, but killing Excel through the Task Manager was the only way to stop the code from running.
Besides, using a "double-click" to run a macro in Excel is just a BAD IDEA. We double-click cells all the time to edit them. This is just BAD!

 

[Fluff]

We double-click cells all the time to edit them.

Not me I use F2, far simpler than using the mouse.

 

[Harry Flashman]

WARNING!

I just tried using this code in one of my Workbooks and it caused Excel to glitch to the point I couldn't use it.
Task Manager didn't show it hung up, but killing Excel through the Task Manager was the only way to stop the code from running.
Besides, using a "double-click" to run a macro in Excel is just a BAD IDEA. We double-click cells all the time to edit them. This is just BAD!

I use this macro to filter crosstabs. However, I restrict the selection range to text labels and not the numeric data. The macro will crash if you double-click a cell containing numeric data.
If you are getting into VBA programming, then I am afraid you will experience bugs from time to time. But it's not the end of the world. If in doubt, always keep a backup of your workbooks when experimenting with VBA.

When you find code snippets on the internet, sometimes (or usually) you will need to make modifications to make the code fit your purpose. In fact, this is how most people get into VBA programming. They start out findings macros on the internet, and they find the need to make adjustments to the code to match their purposes. Also not everyone is looking for complete routines. Many of the people using this forum will be looking for snippets of code. They know how to write VBA but they just need to figure out how to do a particular task (such as filtering a worksheet).

Now as to whether or not you want to use this code, that depends on your workflow. I use it in a workbook with about 100 worksheets of crosstabs and I am reading the data as quickly as possible. I don't generally need to edit the data. But if I did I could either press F2 or just place the cursor in the formula bar. Another option is to disable the code temporarily while making changes (by commenting out the code). So my data will usually have a bunch of product names ets in column A and then the other columns will contain some numeric data. I'll only filter column A using this macro.

Another possibility is adding additional conditions to the code. I mentioned before restricting the selection range. Another possibility would be to check the data type of the cell being selected. So you could add a rule that asks if the cell valule is numeric, if so abort the code etc. The snippet provide above is just a snippet that shows how to use the a worksheet event to filter a range.

The code below is a variation on the above macro that I use in one of my workbooks. It doesn't cause me any problems, but it has additional conditions. But I can't guarantee it will work flawlessly in your situation. If you want to use snippets of code on the internet then you will occasionally need to play around with it to get it to do what you want it to do.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' this version works for any column not just the first column
    Dim Col As Long
    ' If the user double clicks a data value then nothing happens
    If IsNumeric(Target.Value) = True Then Exit Sub
    ' otherwise autofilter is activated
    Col = Selection.Column
    Selection.AutoFilter Field:=Col, Criteria1:=Target.Value
    Range("A1").Select
End Sub

 

[zero269]

Not me I use F2, far simpler than using the mouse.

Hi Fluff,

I think we both understand that the average User clicks in cells far more often than they use the F2 shortcut key... assuming they even use it at all. I supported End Users for many years before switching to System Administration and IT Security... and I can't tell you how many times just mentioning the use of Function Keys to perform certain redundant tasks (F1, F2, F5, F9, etc.) sounded completely foreign to them.

Either way... the WARNING for other people is necessary. It's not for the experienced VBA visitor... it's for all the others.

 

[zero269]

I use this macro to filter crosstabs. However, I restrict the selection range to text labels and not the numeric data. The macro will crash if you double-click a cell containing numeric data.
If you are getting into VBA programming, then I am afraid you will experience bugs from time to time. But it's not the end of the world. If in doubt, always keep a backup of your workbooks when experimenting with VBA.

When you find code snippets on the internet, sometimes (or usually) you will need to make modifications to make the code fit your purpose. In fact, this is how most people get into VBA programming. They start out findings macros on the internet, and they find the need to make adjustments to the code to match their purposes. Also not everyone is looking for complete routines. Many of the people using this forum will be looking for snippets of code. They know how to write VBA but they just need to figure out how to do a particular task (such as filtering a worksheet).

Now as to whether or not you want to use this code, that depends on your workflow. I use it in a workbook with about 100 worksheets of crosstabs and I am reading the data as quickly as possible. I don't generally need to edit the data. But if I did I could either press F2 or just place the cursor in the formula bar. Another option is to disable the code temporarily while making changes (by commenting out the code). So my data will usually have a bunch of product names ets in column A and then the other columns will contain some numeric data. I'll only filter column A using this macro.

Another possibility is adding additional conditions to the code. I mentioned before restricting the selection range. Another possibility would be to check the data type of the cell being selected. So you could add a rule that asks if the cell valule is numeric, if so abort the code etc. The snippet provide above is just a snippet that shows how to use the a worksheet event to filter a range.

The code below is a variation on the above macro that I use in one of my workbooks. It doesn't cause me any problems, but it has additional conditions. But I can't guarantee it will work flawlessly in your situation. If you want to use snippets of code on the internet then you will occasionally need to play around with it to get it to do what you want it to do.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' this version works for any column not just the first column
    Dim Col As Long
    ' If the user double clicks a data value then nothing happens
    If IsNumeric(Target.Value) = True Then Exit Sub
    ' otherwise autofilter is activated
    Col = Selection.Column
    Selection.AutoFilter Field:=Col, Criteria1:=Target.Value
    Range("A1").Select
End Sub

Hi Harry,

I understand your need for your code, and I'll admit my biggest concern is for others... not myself. I ran it in one of my SAMPLE Workbooks out of curiosity based on the fact it filters automatically when you double click on the cell. I wanted to compare that with the one I'm using now:

Sub FilterCellValue()
' Filter Column by Cells Value
    Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

However, due to the fact that it caused Excel to become unstable, and that the OP never reported back with any success or failures... I felt it only responsible to warn others about it. A vast majority of the people in this forum know little to nothing about VBA... and I'd be willing to bet are "clickers" true and true over other cell editing methods.

I use PowerShell and Batch Scripting for my primary needs with Windows... but have only recently gained an interest in using VBA for a personal & educational Excel project for an AR Reading program for students.

Either way... I appreciate your detailed response and hope that other visitors to this thread will benefit from it.

Best regards,