Hiding and unhiding rows

iazcac

New Member
Joined
Sep 26, 2007
Messages
23
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
You need a macro anda button to trigger it, along the lines of

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

iazcac

New Member
Joined
Sep 26, 2007
Messages
23
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
You need another macro:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top