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

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

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

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


"Andrew Duncan" wrote:

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

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

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

No effect on any other worksheet functionality.

Mike

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

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




"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

Mike,

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

Thanks a lot.

Andy


"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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

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

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)


"Andrew Duncan" wrote:

Mike,

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

Thanks a lot.

Andy


"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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

"Andrew Duncan" wrote:

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




"Mike H" wrote in message
...
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


"Andrew Duncan" wrote:

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