how to hide rows if specific cells are empty

sallam · Power User Joined: 03 Mar 2007 Posts: 65

Greetings
Is there a formula that I can use to hide a row if a specific cell in that row has no value?

I have a sheet that I use to issue monthly receipts. I need to make another sheet to produce a list of the unpaid receipts. So I want to set the new sheet so that it makes rows only for the unpaid receipts.

So, is there a formula to instruct the sheet to add new rows only if specific cells have values in them, and ignore the rest?

sallam · Power User Joined: 03 Mar 2007 Posts: 65

help please..

Xelebes3 · Power User Joined: 30 Nov 2008 Posts: 50 Location: Canada

You will need tables. Use Base.

JerzyPiotr · Posted: Thu Jan 08, 2009 2:38 am Post subject:

Try something like this (fill cells as you like, select a range and run this code)

sallam · Power User Joined: 03 Mar 2007 Posts: 65

Thanks very much Jerzy..
I need some details here please:
- where do I add your code? in the input line?
- do I type '=' before I select a range of cells, then paste your code after it?
- what about the equations already in my cells? do I add your code to it, or replace it?
I have in my cells equations like this:

JerzyPiotr · Posted: Thu Jan 08, 2009 9:07 pm Post subject:

Indeed, plenty of questions.
First of all open new calc document, next open Basic IDE (shortcut ALT + F11), next go to left panel end select Standard library in your new document, next activate 3rd button from bottom (this way your insert a new module in your new document), accept the name of the new module and you should be now in your new module ready for programming.
Your should replace the existing few lines with my code in this module (just select my code from web seit, copy to clipboard, return do IDE, select the existing lines and replace them by inserting from clipboard - use Edit/Insert menu because right mouse clik makes no action ).

Last thing, go to a sheet in this new document, fill somme cells in a column leaving somme empty cells between, select them, go back to IDE and run the code (find RUN icon on menu bar or press F5, you can also step through this code - find icon for stepping or press F8 /feight)
For stepping it would be better that you adjust the document and IDE windows so that you can watch them at the same time on the screen.
Enjoy
jerzy

Villeroy · Posted: Fri Jan 09, 2009 12:01 am Post subject:

If A2:F999 is the range in question (A1:F1 having the column labels), you can define arbitrary complex filter criteria in some unused column like this:
G2: =COUNTA($A2:$F2) [counting all non-empty cells]
copy down G2 until the end of list, until G999

Filter by column G=0
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

JerzyPiotr · Posted: Fri Jan 09, 2009 1:55 am Post subject:

It's simple, perfect Villeroy.
If you where interested on only one column (for ex. column A) in a range you should place the formula like this in the Cell G2
=COUNTA($A2:$A2) and copy down
You can also export the result of such a query elsewhere.
Please follow the Villeroy's way and play a little with filters including advanced options where you can select the destination for export of the query. And it's all without having to write a code.
Regards
Jerzy

Villeroy · Posted: Fri Jan 09, 2009 3:00 am Post subject:

For up to 3 columns:
menu:Filter>Standard...
Column A <-not empty->
AND Column B <-not empty->
AND Column C <-not empty->
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

sallam · Power User Joined: 03 Mar 2007 Posts: 65