ExcelBanter - Can i get advanced filter to automatically refresh

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Can i get advanced filter to automatically refresh (https://www.excelbanter.com/excel-worksheet-functions/137533-can-i-get-advanced-filter-automatically-refresh.html)

Can i get advanced filter to automatically refresh

I have set up an advanced filter, but don't seem to be able to get it to
automatically recalculate the filter when i change the number i am filtering
for in the criteris range. Is there a way i can get the filter to
automatically refresh when this criteris is changed?

Thanks in advance.

Answer: Can i get advanced filter to automatically refresh

Yes, you can definitely get the advanced filter to automatically refresh when you change the criteria in the criteria range. Here's how you can do it:

Select the data range that you want to filter.
Go to the "Data" tab in the ribbon and click on "Advanced" in the "Sort & Filter" group.
In the "Advanced Filter" dialog box, select the "Filter the list, in-place" option.
In the "Criteria range" field, select the range of cells that contain your filter criteria.
Check the "List range" field to make sure it includes the entire data range you want to filter.
Check the "Unique records only" box if you want to filter for unique values only.
Click on the "OK" button to apply the filter.

Now, to make the filter automatically refresh when you change the criteria in the criteria range, you need to use a little bit of VBA code. Here's how:

1. Press "Alt + F11" to open the Visual Basic Editor.
2. In the Project Explorer window, double-click on the worksheet that contains your data range.
3. In the code window, paste the following code:

Formula:


		
			
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("CriteriaRange")) Is Nothing Then
        Range("DataRange").AdvancedFilter Action:=xlFilterInPlace
    End If
End Sub

4. Replace "CriteriaRange" with the range of cells that contain your filter criteria, and replace "DataRange" with the range of cells that contain your data.
5. Save the workbook and close the Visual Basic Editor.

Now, whenever you change the criteria in the criteria range, the filter will automatically refresh and show the updated results. No need to manually reapply the filter every time you make a change.

Can i get advanced filter to automatically refresh

You can update the filtered list by using programming. There are
examples he

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0001 - Product List by Category'

RichCovo wrote:
I have set up an advanced filter, but don't seem to be able to get it to
automatically recalculate the filter when i change the number i am filtering
for in the criteris range. Is there a way i can get the filter to
automatically refresh when this criteris is changed?

Thanks in advance.

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Can i get advanced filter to automatically refresh

Debra Dalgleish wrote...
You can update the filtered list by using programming. There are
examples he

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0001 - Product List by Category'
....

You only use a Change event handler. That may be sufficient in this
instance, but it's safer to use a Calculate event handler too, with
the Calculate event handler using a static variable to check for state
changes in specific cells to determine whether it needs to refresh the
filter. Both the Calculate and Change event handlers would call a
common private subprocedure to refresh the filter.