 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2004Hide rows with zero value?
just wondering how I would be able to hide rows on sheets if some cells are blank? the cells contain formulas they are only blank because `zero values` in >tools>options>view is unchecked. I have a sheet set up to SUMIF values from about 16 other sheets, and it returns the values depending on 72 corresponding codes, I would like to hide any of the 72 rows of data that are blank. the values returned will change on a week to week and daily basis, so I would like to be able to use the same design for each new week, hope that makes sense. Hi try something like the following: Sub hide_rows() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "A").Value = "" Then Rows(RowNdx).hidden = True End If Next RowNdx End Sub hides all blank rows in column A  Signature-- Regards Frank Kabel Frankfurt, Germany > just wondering how I would be able to hide rows on sheets if some > cells are blank? the cells contain formulas they are only blank [quoted text clipped - 8 lines] > I would like to be able to use the same design for each new week, > hope that makes sense. Hi Steven A good way to do this is via Data>Filter>Auto Filter. Hide the un-needed rows, then go to View>Custom Views and "Add" the View with a name like "HideZero". Next time you need it simply show the View or Record a macro showing it. The good thing is you don't need Auto Filters applied before showing the saved View. ....or, if Colulmn "A" has the cells to check for Zero, use some code like; Sub HideZeros() Dim rRange As Range For Each rRange In Range("A1", Range("A65536").End(xlUp)) rRange.EntireRow.Hidden = _ (rRange = 0 And Len(rRange) = 1) Next rRange End Sub Dave when i high light the rows and go to data filter, autofilter is not an option i can use. i need something that will say look at colum I, (I contains totals of rows B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero value, hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud :) > just wondering how I would be able to hide rows on sheets if some cells are > blank? the cells contain formulas they are only blank because `zero [quoted text clipped - 7 lines] > would like to be able to use the same design for each new week, hope that > makes sense. So select just Col I (Or the cell with your header in, down to the last cell of data), do Data / Filter / Autofilter and then Filter on 'Not Equal to 0' SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- > Dave when i high light the rows and go to data filter, autofilter is not an > option i can use. [quoted text clipped - 16 lines] > > would like to be able to use the same design for each new week, hope that > > makes sense. A couple of reasons that Data|filter|Autofilter isn't available: 1. Your worksheet is protected (tools|Protection|unprotect worksheet to check. You may need a password.) 2. Your worksheet is grouped with another worksheet. Check the title bar. Do you see something like "book1.xls [Group]" If yes, right click on a worksheet tab and select "Ungroup Sheets" > Dave when i high light the rows and go to data filter, autofilter is not an > option i can use. [quoted text clipped - 16 lines] > > would like to be able to use the same design for each new week, hope that > > makes sense. SignatureDave Peterson ec35720@msn.com Its not protected or grouped > A couple of reasons that Data|filter|Autofilter isn't available: > [quoted text clipped - 25 lines] > > > would like to be able to use the same design for each new week, hope that > > > makes sense. Either we are all talking by each other here, or something is screwy somewhere. This is something that would usually be a 2 second job to most of the regulars, so I know we're not all losing it. Are you able to send me the file at all so I can take a look first hand, only we've pretty much covered most things it could be?? ken.wright [at] ntlworld.com SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip> What happens when you try it? Is it just disabled or does something else happen (error message)? If you try it against a test worksheet in a new workbook, does it work? > Its not protected or grouped > [quoted text clipped - 41 lines] > > Dave Peterson > > ec35720@msn.com SignatureDave Peterson ec35720@msn.com Dave Not Shared Not Grouped Sheet not Protected Workbook not protected Track changes not enabled Autofilter greyed out, but Advanced Filter available as an option - Same on every part of the worksheet(s)/book I tried All Tools / Trace Options greyed out Putting Autofilter on another sheet in a new book and moving the sheet to this book results in loss of the Autofilter, BUT, Data / Filter / Autofilter is checked albeit greyed out?? ?? SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip> com Hi Ken this sounds as if the workbook is somehow corrupted (very interesting). Signature-- Regards Frank Kabel Frankfurt, Germany > Dave > [quoted text clipped - 11 lines] > > ?? And if I move the test sheet I put into the book back out of the book where it came from, the autofilter is reactivated?? SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip> One more thing to try -- Choose Tools>Options On the View tab, under Objects, select Show All, or Show Placeholders. > Its not protected or grouped > [quoted text clipped - 55 lines] > >>>>makes sense. SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Oooooh. I like this one! > One more thing to try -- > Choose Tools>Options [quoted text clipped - 69 lines] > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html SignatureDave Peterson ec35720@msn.com Deb, YOU ARE JUST TOO DAMNED GOOD AT THIS!!!!!!!!!!!!!!!!! <vbg> That fixed it :-) SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- > One more thing to try -- > Choose Tools>Options [quoted text clipped - 59 lines] > > > >>>>makes sense. Thanks everyone for your help! xxx > One more thing to try -- > Choose Tools>Options [quoted text clipped - 59 lines] > > > >>>>makes sense. Debs could you explain what show placeholders does? i do not understand what this had to do with autofilter not showing? Thanks :) Steve > One more thing to try -- > Choose Tools>Options [quoted text clipped - 59 lines] > > > >>>>makes sense. The arrows in the AutoFilter heading cells are hidden if objects are hidden. So, if objects are hidden before the AutoFilter is in place, Excel won't allow you to create an AutoFilter (I'm not sure if that's a bug or a feature). If Show Placeholders is selected, some objects, such as charts, are represented by an outline. Other objects, like AutoFilter arrows, are visible, so when that option is selected, you can create or use an AutoFilter. > Debs could you explain what show placeholders does? i do not understand what > this had to do with autofilter not showing? [quoted text clipped - 68 lines] >>> >>>>>>makes sense. SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.