'Hide rows with formulas but no data' - Sheet Event Code problem

Hi Folks,

I've just got my first macro working!! Admittedly with code I found an another forum.

Its this Sheet event code:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Hide rows with formulas but no data'
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub



This works fine for some of my worksheets as I am only interested in a single column. However on another worksheet I have 2 columns (A and H) with values returned by formulas but which are on their own row that I would like to hide if they return '0' value.

i.e.

A16 returns a value, then H17,18,19,20,21,22,23 also return values.

Then back to A25 with a returned value and then H26,27,28,29,30,31,32 with returned values

and so on and so on.


Can the above code be adapted to 'look' in both these columns?

Thanks very much in advance

Try this .
Change

Please Login or Register  to view this content.

By

Please Login or Register  to view this content.

Originally Posted by Debraj Roy

Try this one..

Please Login or Register  to view this content.

Thanks Debraj,

This works on hiding '0' values on column A and the sub '0' values on column H but it does not hide the '0' value ' cells on column H that are 'under' a filled in values on column A.

I.e, using my example above:


Main Value: A16 might be 5
so sub Values in column H break out what makes that value in A16, i.e. H17 might be '2', 18 might be '0', 19 might be '1', 20 might be '2', 21 might be '0' ,22 might be '0', 23 might be '0' .

Then onto next set of values:

Main Value: A25 might be 0
Sub values: H26,27,28,29,30,31,32 = all 0 as well therefore.

Your macro suggestion is currently removing all A25 and H26 to H32 rows but it is still showing H18, H21, H22 and H23 rows

Any ideas?

Thank you.

I cant download this at work (don't ask!).

I'll try this eve and come back to you.

Thank you.

Originally Posted by kvsrinivasamurthy

Try this .
Change

Please Login or Register  to view this content.

By

Please Login or Register  to view this content.

Thanks for this kvsrinivasamurthy!

I've tried your suggestion in Debraj's macro but it throws up a bug.

See my comment to his post.

Do you know what might fix this?

Thanks very much again.

Originally Posted by Winon

Hi OLLY-7,

Maybe you would like to try the attached sample WorkBook.

It takes care of any Column with Formulas.

Also see the "Please consider" note at the bottom of this post.

I cant download this at work (don't ask!).

I'll try this eve and come back to you.

Thank you.

Originally Posted by Winon

Hi OLLY-7,

Maybe you would like to try the attached sample WorkBook.

It takes care of any Column with Formulas.

Also see the "Please consider" note at the bottom of this post.

Thanks for this Winon.

Its pretty cool for sure only it does the opposite of what I am hopping it will do! I need all formals that return a '0' value to hide the rows and keep those that return a positive value.

can it be tweaked to do this do you think?

Thanks again.

Sorry to sound daft - but where do I put that code?

Cheers.

Hi Winon,

Thanks very much for this. Works a treat.

Sorry again to sound dumb, but would you be able to explain how to assign this, or any macro, to a new button on my spread sheet.

I've been trying and not sure the correct procedure. Do I need to reference the sheet name in the macro code for each button and so have a separate macro therefor for each sheet?


Thanks very much again. This is a massive help!

Originally Posted by Winon

Hi OLLY-7,

No problem. See the revised attached Workbook.

To view the Code, open the Workbook and then, hold down Alt and press F11.