Conditional Hide

[Admin @ Aust-Mech]

I wan needing to hide certain rows in a spreadsheet, but not the same ones
each time. I was thinking I may be able to do something like
if the number is cell O = zero then hide that row.

is this possible or is there another way to do it. It is costing me alot of
time at the moment!

Thanks for your time
Helen

 

[JBeaucaire]

Yes, it's doable, usually with a worksheet macro of several choices. The
thing you need to decide is if this should happen in realtime on its own,
which would make it difficult to unhide said rows, or if you want it to occur
when you manually run the macro.

 

[JBeaucaire]

Here's an on-demand macro you can run to do it:

========
Sub CheckColumnO()
Dim lastrow As Long, rng As Range, cell As Range
lastrow = ActiveSheet.UsedRange.Rows.Count
Set rng = ActiveSheet.Range("O1:O" & lastrow)

For Each cell In rng
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell

End Sub
=========

 

[Admin @ Aust-Mech]

Thanks for your advice and effort. I tried the macro you suggested and it
doesn't work. It tends to flick over a few rows repetatively, changes my 0 to
1 and then says 'out of stack space'.

I will admit that I have never written a macro before so my debugging skills
are non-existant.

I certainly appreciate your help and if you have any other ideas, they will
be gratefully received.

Thanks

 

[JBeaucaire]

I am testing this on a sheet where I'm just putting 1, 0 and blanks down the
sheet a ways, then running the macro. It hides all the rows with blank or
zero, so the only thing I can think is typo. Be sure to try cut-n-paste from
this message instead of retyping, or get it working on a small sheet first
like I've done above. Once you know it's actually working, try it again on
your larger data set.

 

[David Biddulph]

AutoFilter

 

[Shane Devenshire]

To Clarify Davids suggestion:

In 2003 select the column which contains the 0's and choose Data, Filter,
AutoFilter. Then open the auto filter drop down and choose Custom, from the
first box on the left choose does not equal, in the second box on the right
enter 0 or whatever you don't want to see and click OK.

In 2007 the same idea just choose Data tab, Filter, then open the filter
drop down and choose Number Filters, Does Not Equal...

Note if you have O rather than 0 then you will see Text Filters in 2007
instead of Number Filters.