SOLVED
Home

Macro to automatically Filter Results

%3CLINGO-SUB%20id%3D%22lingo-sub-1093609%22%20slang%3D%22en-US%22%3EMacro%20to%20automatically%20Filter%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093609%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20series%20of%20data%20which%20is%20linked%20to%20one%20tab.%20I%20have%20seperated%20some%20information%20from%20this%20tab.%20I%20am%20hoping%20then%20i%20can%20add%20to%20the%20Macro%20to%20automatically%20filter%20results.%20For%20example%20i%20only%20want%20results%20which%20were%20Yes%20in%20Column%20H%20to%20be%20included.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1093609%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1095775%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20automatically%20Filter%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1095775%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20found%20the%20below.%20Will%20this%20work%20for%20the%20requirements%20below%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22k%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ESub%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22nf%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3Efilter_on_department%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E()%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22k%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3EDim%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22nv%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3Erange_to_filter%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22ow%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3EAs%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ERange%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22k%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ESet%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3Erange_to_filter%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22o%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%3D%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ERange%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E(%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%22H%3AH%22%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E)%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3Erange_to_filter%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3EAutoFilter%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3EField%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22o%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%3D%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22mi%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E1%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22n%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ECriteria1%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22p%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22o%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%3D%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3E%22Yes%22%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22k%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3EEnd%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22k%22%3E%3CSPAN%20class%3D%22blast%20mmt-sentence%22%3ESub%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1095987%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20automatically%20Filter%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1095987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20only%20one%20line%20of%20code%20to%20filter%20the%20column%20H%20which%20is%20like%20this...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20filter_on_department()%0ARange(%22A1%22).CurrentRegion.AutoFilter%20field%3A%3D8%2C%20Criteria1%3A%3D%22Yes%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBtw%2C%20you%20have%20placed%20the%20event%20codes%20on%20Standard%20Modules%20which%20is%20not%20the%20right%20place%20for%20them.%3C%2FP%3E%3CP%3EYou%20should%20place%20the%20Worksheet_Change%20event%20code%20on%20the%20Sheet%20Module%20and%20Workbook_Open%20event%20code%20on%20ThisWorkbook%20Module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1097606%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20automatically%20Filter%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097606%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20kindly%20for%20your%20assistance.%20I%20have%20updated%20the%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%20always%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1097826%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20automatically%20Filter%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097826%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent 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,

4 Replies
Highlighted

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 

Highlighted
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.

 

Highlighted

Hi@Subodh_Tiwari_sktneer 

 

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

 

Thanks again, always much appreciated.

Highlighted

You're welcome @calof1!

Related Conversations
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Filter complex table to simple table
Sam2009 in Excel on
1 Replies
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Macro issue with email connectivity
unhappyuser in Excel on
21 Replies