Hi,
I am not sure if i am close but i am attempting to hide a range of rows based on the value of another cell. To be more clear A11 value is "Status" and i need to evaluate B11 and if it is "Open" then hide 11 rows up. This repeats in a dynamically lengthed document that i run weekly. Below is what i have tried - i am sure it is not as efficient as it could be but it is a starting point. Any help or suggestions would be greatly appreciated.
Thank you!!
I am not sure if i am close but i am attempting to hide a range of rows based on the value of another cell. To be more clear A11 value is "Status" and i need to evaluate B11 and if it is "Open" then hide 11 rows up. This repeats in a dynamically lengthed document that i run weekly. Below is what i have tried - i am sure it is not as efficient as it could be but it is a starting point. Any help or suggestions would be greatly appreciated.
Code:
Sub Closed_Susp()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleID = "Status Tool"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleID, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step -1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "Status:" And Rng.Offset(0, 1).Value = "OPEN" Then
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(-11, 0)).EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
Thank you!!