How To Autofilter Rows Based On Cell Value In Excel?

Rashie

New Member
Joined
Jun 5, 2015
Messages
42
Hey All,

I'm having a nightmare with this seemingly simple task.

I have several cells as below
E USD
E GBP
E EUR5
O USD
O USD
O USD

<tbody>
</tbody>

<tbody>
</tbody>

And another table of data below with a frozen pane, A10:Z2500.
Using an example of E EUR this data is in column J, O USD is in column K etc
I just want to use a filter function where instead of using the auto filter I can enter an amount next to each cell as above and have it filter automatically.

I can make it work individually but I can't have all 6 functional at the same time.

Any ideas?

Thanks All :)

Sorry, should have posted my semi successful code too. I just want to have this working at the same time for all 6 data cells.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngData As Range
Dim lngCol As Long

'----SETUP---
'Which cell are you typing in?
Set rngTrigger = Range("B3")
'Where is the data to be filtered?
Set rngData = Range("A10:Z2500")
'what number column are we filtering of the table?
lngCol = 1
'------------

'Check if user changed our cell of interest?
If Intersect(Target, rngTrigger) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
With rngData
'Clear previous filter
.AutoFilter
'Apply new filter
.AutoFilter field:=lngCol, Criteria1:=rngTrigger.Value
End With

Application.ScreenUpdating = True
End Sub
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
The code says that you filter in column 1, but in your explanation you want to filter in column J, or I'm not really understanding what you need.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,887
Members
410,643
Latest member
sng
Top