Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Admin @ Aust-Mech" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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 ========= -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Admin @ Aust-Mech" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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" wrote: 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 ========= -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Admin @ Aust-Mech" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Admin @ Aust-Mech" wrote: 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" wrote: 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 ========= |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
AutoFilter
-- David Biddulph Admin @ Aust-Mech wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Hide
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "David Biddulph" wrote: AutoFilter -- David Biddulph Admin @ Aust-Mech wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I hide a row conditional upon a cell value? | Excel Worksheet Functions | |||
Conditional Format - Hide/Unhide | Excel Worksheet Functions | |||
CONDITIONAL HIDE/UNHIDE ROWS | Excel Discussion (Misc queries) | |||
Using VBA to Conditional Hide Rows? | Excel Discussion (Misc queries) | |||
Hide cell value conditional on its value | Excel Worksheet Functions |