SOLVED

Macro to automatically Filter Results

Iron Contributor

Hi,

 

I have a series of data which is linked to one tab. I have seperated some information from this tab. I am hoping then i can add to the Macro to automatically filter results. For example i only want results which were Yes in Column H to be included.

 

Can someone please assist.

 

Many thanks,

10 Replies

Hi All,

 

I have found the below. Will this work for the requirements below?

 

Sub filter_on_department()
Dim range_to_filter As Range
Set range_to_filter = Range("H:H")

range_to_filter.AutoFilter Field:=1, Criteria1:="Yes"

End Sub

@calof1 

best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

You need only one line of code to filter the column H which is like this...

Sub filter_on_department()
Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub

 

Btw, you have placed the event codes on Standard Modules which is not the right place for them.

You should place the Worksheet_Change event code on the Sheet Module and Workbook_Open event code on ThisWorkbook Module.

 

Hi@Subodh_Tiwari_sktneer 

 

Thank kindly for your assistance. I have updated the worksheets.

 

Thanks again, always much appreciated.

You're welcome @calof1!

Hi@Subodh_Tiwari_sktneer 

 

Thanks for your help with the filter. 

 

I notice this works when i click "Run", however is there a way to make it "Run" whenever new data is added?

 

Many thanks,

@calof1 

Which sheet do you want to filter based on the "Yes" criteria in Column H?

Are you entering "Yes" manually in column H or it is being populated with the help of a formula?

Hi @Subodh_Tiwari_sktneer 

 

Thanks for the reply.

 

For this example on the "High Risk Register" Tab column H is "PEP". I wish the filter to show only results for "Yes". The information to this comes from the "Queries" tab via formula.

 

Please let me know any other details required.

 

Many thanks,

@calof1 

For that first place the following code on a Standard Module like Module1...

Sub filter_on_department(ws As Worksheet)
ws.Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub

 

Then place the following code on Queries Sheet Module (not on standard module)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

Dim wsToFilter As Worksheet

Set wsToFilter = Worksheets("High Risk Register")

If Target.Column = 9 And Target.Row > 1 Then
    filter_on_department wsToFilter
End If

End Sub

 

So now as per the above codes, once you select either Yes or No in column I on Queries Sheet, the data on High Risk Register would be automatically filtered.

Thank you kindly again@Subodh_Tiwari_sktneer 

 

This works exactly as desired, thank you for continuing to be a real asset to this comunity.

 

Thanks,

@calof1 

You're welcome!

1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

You need only one line of code to filter the column H which is like this...

Sub filter_on_department()
Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub

 

Btw, you have placed the event codes on Standard Modules which is not the right place for them.

You should place the Worksheet_Change event code on the Sheet Module and Workbook_Open event code on ThisWorkbook Module.

 

View solution in original post