Hiding and unhiding rows

[iazcac]

I have what I think is a simple problem but have hit a brick wall!!

I have two columns :

Column A - Date Completed
Column B - Status

It is set up that when column A is blank, Column B displays "OPEN"

Once Column A has a date in it, then Column B displays "CLOSED"



What I require is that once Column B displays "CLOSED" the row becomes hidden. I have managed to do this with the following code:


Private Sub Worksheet_Change
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 And _
Target.Value = "CLOSED" Then
Target.EntireRow.Hidden = True
End If
enditall:
Application.EnableEvents = True
End Sub

However, what I then require is a button to show all records again as sometimes closed records need to be viewed again. I then require a second button to hide all "CLOSED" records again

Any help greatly appreciated

Thanks
C

 

[Johnny C]

You need a macro anda button to trigger it, along the lines of

Sub Show_all
Activesheet.Cells.EntireRow.Hidden = False
End Sub

 

[iazcac]

Hi,

That is great for first part to show all records. Thanks.

How do I then just hide the "CLOSED" records again?

Thanks

 

[Johnny C]

You need another macro:

Sub ChangeitBack
 
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
for rowcount = 2 to LastRow
If Cells(rowcount,4).Value = "CLOSED" Then cells(rowcount,4).EntireRow.Hidden = True
End Sub