Hide Rows based on 3 column variables

[agnesz]

Hi there -

I need help with a macro that will hide a row based on two conditions:
1) if column Y <= 1.0 and column x <= 5.0 -> row should be hidden
2) if column Y >= 1.0 and column x <= 5.0 -> row should be showing
3) if column Y >= 1.0 and column x >= 5.0 -> row should be showing

hope this makes sense...
anybody know how to help me out
THANKS VERY VERY MUCH

[Bob Phillips]

What if Y=1?

What if Y<=1, x>5.0?

[agnesz]

valid point!

if y = 1, entire row hide
if y <=1 and x >=5, row should show up

[Bob Phillips]

Public Sub ProcessData()
Dim oRow As Range

With ActiveSheet

For Each oRow In .UsedRange.Rows
If .Cells(oRow.Row, "Y").Value = 1 Then
oRow.EntireRow.Hidden = True
ElseIf .Cells(oRow.Row, "Y").Value <= 1 And _
.Cells(oRow.Row, "X").Value <= 5 Then
oRow.EntireRow.Hidden = True
Else
oRow.EntireRow.Hidden = False
End If
Next oRow

End With

End Sub

[geekgirlau]

Depending on the amount of data you're dealing with, would it not be quicker to perform an advanced filter on the data?

[agnesz]

thank you for that code, it works perfectly! however, how can i adjust it to only start looking at column y in row 5?

I have the title and other info in the first few rows of the spreadsheet...

An advanced filter would probably be better, however this code is for an automated report which I want to make as hands-off for the user as possible.

THANKS AGAIN!

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count,"Y").End(xlUp).Row
For i = 5 To iLastRow
If .Cells(i, "Y").Value = 1 Then
.Rows(i).Hidden = True
ElseIf .Cells(oRow.Row, "Y").Value <= 1 And _
.Cells(i, "X").Value <= 5 Then
.Rows(i).Hidden = True
Else
.Rows(i).Hidden = False
End If
Next i

End With

End Sub