greater than and less than filtering <x>

[rob179994]

Hi

I am having trouble filtering greater than in one range and less than in another range at the same time. If i separate them, they work separately but not at the same time. What is going on and can anyone help. Here is the code below.

Sub Filtergreater()




Sheets("FolderSort").Select
ActiveSheet.AutoFilterMode = False
Range("I4:I368").AutoFilter field:=1, Criteria1:=">" & Sheets("FrontPage").Range("E17").Value
Range("J4:J368").AutoFilter field:=1, Criteria1:="<" & Sheets("FrontPage").Range("K17").Value


End Sub



Thanks in advance.

 

[Andrew Poulsom]

What does the macro recorder give you if you record while filtering manually?

 

[rob179994]

Well the thing is, you can't manual filter for greater than or less than. You can only filter for specific values in the column.

 

[rob179994]

Any one else got any other ideas? I just cant seem to spot the problem.

 

[Andrew Poulsom]

If you can't do it manually you won't be able to do it in VBA. It sounds like you need Advanced Filter rather than AutoFilter.

 

[Jonmo1]

Well the thing is, you can't manual filter for greater than or less than. You can only filter for specific values in the column.

You can't filter 2 seperate columns on an OR basis, only by AND.

What I would do is put a formula in an available column
So for example, in M4
=OR(I4>$E$17,J4<$K$17) and fill down to M368

Then filter column M for TRUE

 

[rob179994]

Yeah i like the idea of that true and false thing but the only problem is the values being used from cells E17 and K17 are on the sheet before, how do i factor this in to the formula and can this formula be written in vba code?

Thanks, much appreciated!

 

[Jonmo1]

You're welcome.

You can add in the sheet references as needed

=OR(I4>'FrontPage'!$E$17,J4<'FrontPage'!$K$17)