Advanced Filter From Varying Criteria Range

[Metserv]

Greetings Experts ! I am a VBA novice and kindly seek help with advanced filter.I want to filter a range of values from a sheet named "Data" based on a criteria range from a sheet named "Criteria".For example,

Sheet ("Data") Sheet("Criteria")
A A
Region Region
1. East 1. East
2. South 2. West
3. East
4. Central
5. North

The criteria range should vary, so it should be read up to the last value in the column.I want the list filtered in place.If a value in the "Data" range does not fall within the "Criteria" Range, then that row is hidden, e.t.c

Thank you!

[Bob Phillips]

Post the workbook.

[mantooth29]

Try this


Sub DynamicAdvFilter

Dim rData as Range, rCriteria as Range
Set rData = Thisworkbook.Sheets("Data").Range("A1")
Set rCriteria = Thisworkbook.Sheets("Criteria").Range("A1")

rData.CurrentRegion.AdvancedFilter, xlFilterinPlace, rCriteria.CurrentRegion, , False

'currentregion gets everything that is connected to the starting cells ("A1") in 'this case. Please note it will get all columns and rows in your criteria sheet that 'would logically be connected to the starting cell. To test if this will meet your 'needs, I have provided the following lines...

Sheets("Criteria").activate
rCriteria.CurrentRegion.Select

'if the range you want is selected at the end of the sub, then delete the two lines 'above. If not, you may need something a little less dynamic that only uses one 'column

End sub

[kattai_engg]

Hi,
i am new to this VBA code. some what i manage using record macro to copy the values from 1st sheet to 2nd sheet. The problem is i created a macro for each letters for column H10 (drop down list - purpose is which row show 0 it will copy the row and paste to 2nd sheet where i need that). i need a single macro to combine all macros or using a single macro itself i need a result. here is some limitaion i need , while seeing the Colum H there must be the user colud select the values to run the macro oterwise the Msg box state that there will be no seletion please assign the values. one more problem which is i created macro, that is there is no C1 in the column H Drop down seletion still i need to run the maro for 0, like wiese i need that up to C4. means suppose there is the C4 the user can select atleast one row contain the C2, C3 & 0 in the Column H. i also attached the sheet. adnvace thanks for your help.
thanks
kattai