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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,040
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,040
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,033
Messages
5,856,941
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top