Hide / UnHide - is there a Function to automate this ?

A

Andrew Duncan

Hello all, does anyone know if there is a way to create a function to
Automatically Hide a Row based on a formula

In thise case if a Cell in a row (a1 for arguements sake) = 0

It would help me tidy up a work book if I could do this.

Thanks Andy
 
M

Mike H

Andrew,

You could monitor A1 with code and if it is set to a value you want then
hide rows:
Right click the worksheet tab - view code and paste this in.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If Target.Value = 0 Then
Rows(4).Select '< Change to suit
Selection.EntireRow.Hidden = True
End If
End If

End Sub

If A1 is set to zero it hides row 4 but you can change the ranges and values
to suit.

Mike
 
A

Andrew Duncan

Thanks Mike,

And if I wanted to monitor rows from 1 to 250 and on the same basis hide any
rows that are empty Would the following work:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A1:$A250" Then
If Target.Value = 0 Then
Rows(4).Select '< Change to suit
Selection.EntireRow.Hidden = True
End If
End If

End Sub


Would the usual 'Hide' and 'UnHide' (right clicking ) also work or would
that now be disabled ?

Andy
 
M

Mike H

Sorry I missed your question. No that wont work, to monitor a range of cells
try:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) _
Is Nothing Then Exit Sub
If Target.Value = 0 Then
Rows(4).Select '< Change to suit
Selection.EntireRow.Hidden = True
End If
End Sub
 
A

Andrew Duncan

Mike,

Thanks for your patience.
I have a spreadsheet with 507 rows in it.
At various times when accessed other worksheets may or may not of inputted
data in to some or all of those rows.
i.e.

row A
6 clive
7 0
8 apple
9 pear
10 0
11 0
12 0
13 kart
14 bike
15 0


Rather than manually hidding or unhiding the rows with no data in column A
of the Rows can we create the code to review rows 6 through 507 and hide any
that have Zero in the A column ?

Andy
 
M

Mike H

Andrew,

Does this work?

Sub marine()
For x = 507 To 1 Step -1
Cells(x, 1).Select
If ActiveCell.Value <> "" And ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = True
End If
Next
End Sub

Mike
 
A

Andrew Duncan

Mike,

Yes it seems to of worked (although it chugged away doing it one row at a
time !)

Thanks a lot.

Andy
 
J

JMB

Any reason not to use the Autofilter? Click Data/Filter/Autofilter - click
the drop arrow, select "Custom" and use a criteria of

Does Not Equal 0
AND
Does Not Equal (leave criteria empty so blank cells also filtered out)
 
Top