VBA - Hide Rows based on two Cell Values

[ATonchev]

Hello,

I have a table with two columns (min value and max value) and I want to filter the rows typing in the desired range in two separate cells.
I have achieved to make it for the first column. It is as follows:


Sub SearcRange()

'Hide
Dim Cell As Range
Var = Range("C1").Value
Application.ScreenUpdating = False
Range("AL9:AL500").End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
For Each Cell In Range(ActiveCell, "AL9:AL500")
Cell.EntireRow.Hidden = Cell.Value >= Var
Next

Application.ScreenUpdating = True

End Sub

Actually, I need the result to be filtered again according column AM9:AM500 taking the value from cell C2.

Can you help me please?

Cheers,
Anton

 

[Yongle]

Try this
With MIN in C1 and MAX in C2

Sub HideRows()
    Dim Cell As Range, Data As Range, Rng As Range
    Set Data = Range("AL9:AL500")
    Set Rng = Range("AL" & Rows.Count)
    For Each Cell In Data
        If Cell < Range("C1") Or Cell > Range("C2") Then Set Rng = Union(Rng, Cell)
    Next
    Data.EntireRow.Hidden = False
    If Rng.Count > 1 Then Intersect(Rng, Data).EntireRow.Hidden = True
End Sub

 

[ATonchev]

Hi Yongle,

Thank you very much for your answer!

I am afraid that the macro doesn't work as I expected. I wanted the value taken from C2 to be filtered according column AM9:AM500. I tried to fix it by myself, but meet the same problem as in my initial macro.

I will be very grateful if you can help me again!

Cheers,
Anton

 

[Yongle]

1. Are you saying that you do not want rows hidden, but ONLY want them filtered ?
2. Are filter criteria <=MAX (value C2) and >=MIN (value in C1) ?

 

[ATonchev]

Sorry, maybe I was no 100% clear.

I want only that rows to be shown which the value from column AL9:AL500 is bigger than C1 and the value from column AM9:AM500 is smaller than C2. So the logic is as follows:

when the value from AL9:AL500 < C1 and value from AL9:AL500 < C1 = hide rows
when the value from AL9:AL500 < C1 and value from AL9:AL500 > C1 = hide rows
when the value from AL9:AL500 > C1 and value from AL9:AL500 > C1 = hide rows
when the value from AL9:AL500 > C1 and value from AL9:AL500 < C1 = show rows

Thank you ones again!

 

[Yongle]

why is everything in your conditions C1 ?
where is C2 ?

 

[ATonchev]

OMG!!! Sorry!

when the value from AL9:AL500 < C1 and value from AL9:AL500 < C2 = hide rows
when the value from AL9:AL500 < C1 and value from AL9:AL500 > C2 = hide rows
when the value from AL9:AL500 > C1 and value from AL9:AL500 > C2 = hide rows
when the value from AL9:AL500 > C1 and value from AL9:AL500 < C2 = show rows

 

[ATonchev]

Still not true I need a rest ...

Finally:

when the value from AL9:AL500 < C1 and value from AM9:AM500 < C2 = hide rows
when the value from AL9:AL500 < C1 and value from AM9:AM500 > C2 = hide rows
when the value from AL9:AL500 > C1 and value from AM9:AM500 > C2 = hide rows
when the value from AL9:AL500 > C1 and value from AM9:AM500 < C2 = show rows

 

[Yongle]

To EXCLUDE the values in C1 and C2 (as requested in post#8)

Sub FilterBetweenValues()
    With ActiveSheet.Range("$AL$8:$AL$500")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=">" & Range("C1"), Operator:=xlAnd, Criteria2:="<" & Range("C2")
    End With
End Sub

To INCLUDE the values in C1 and C2

        .AutoFilter Field:=1, Criteria1:=">=" & Range("C1"), Operator:=xlAnd, Criteria2:="<=" & Range("C2")

Note : AL8 is included in the range deliberately - it is treated as the header