Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria ran

actuarish · Mar 14th 2013

Greetings.

Im trying to autofilter a data range with the value of a specific cell as the criteria range, i.e filter a range based on the value of a cell. Here is my code, but it doest work.

Code

Sub Filter()


Dim k as integer
'k is value on cell B2 and the criteria range. The criteria range should be ">k"
k = Worksheets("Sheet1").Range("B2").Value
'Ive also tried Set k = Worksheets("Sheet1").Range("B2").Value but it also doesnt work
Columns("D:D").Select
Selection.AutoFilter
ActiveSheet.Range("D1:D500").AutoFilter Field:=1, Criteria1:=">k", Operator:=xlAnd
End sub

Display More

Please intervene.

Thanks

venkat1926 · Mar 16th 2013

Re: Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria

k is an inbteger so it should NOT be "set K=" it shouyld be as you have done ""k="(without quote maraks)

whenyou write criteria ">k" it menas greater than >k which is a string and it doe not take k as variable

it shoud be
Criteria1:=">" & k

whever you use variable remember this

Code

Sub Filter()
     
    Dim k As Integer
     'k is value on cell B2 and the criteria range. The criteria range should be ">k"
    k = Worksheets("Sheet1").Range("B2").Value
     'Ive also tried Set k = Worksheets("Sheet1").Range("B2").Value but it also doesnt work
    Columns("D:D").Select
    Selection.AutoFilter
    ActiveSheet.Range("D1:D500").AutoFilter Field:=1, Criteria1:=">" & k, Operator:=xlAnd
End Sub

also check whether there are values in col.D values mnore than B2. if noneis thgere in filter only heading row will come up

Smallman · Mar 16th 2013

Re: Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria

This will work just as well.

Code

Sub Filter()    
Range("D1:D500").AutoFilter 1, ">" & Sheet2.[B2]
End Sub

Take care

Smallman

actuarish · Mar 22nd 2013

Re: Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria

My apologies for taking time to reply. Many THANKS, those two ideas both worked perfectly !!

Thanks once again.

Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria ran

Participate now!

Share