OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

how to hide rows if specific cells are empty

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sallam
Power User
Power User


Joined: 03 Mar 2007
Posts: 65

PostPosted: Thu Jan 01, 2009 4:50 am    Post subject: how to hide rows if specific cells are empty Reply with quote

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?
Back to top
View user's profile Send private message
sallam
Power User
Power User


Joined: 03 Mar 2007
Posts: 65

PostPosted: Tue Jan 06, 2009 10:11 am    Post subject: Reply with quote

help please..
Back to top
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Tue Jan 06, 2009 10:46 am    Post subject: Reply with quote

You will need tables. Use Base.
Back to top
View user's profile Send private message
JerzyPiotr
General User
General User


Joined: 03 Sep 2005
Posts: 32
Location: Poland

PostPosted: Thu Jan 08, 2009 2:38 am    Post subject: Reply with quote

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

REM  *****  BASIC  *****
Option Explicit

Sub Main
dim oRanges,oRange
Dim oCell,oCells
   oRange = ThisComponent.CurrentSelection
Dim oEmptyRanges
   oEmptyRanges = oRange.queryEmptyCells ( )
Dim oEmptyRange
   For Each oEmptyRange in oEmptyRanges
      oEmptyRange.Rows.IsVisible = False
   Next
   
   Exit Sub
   
End Sub

Jerzy
Back to top
View user's profile Send private message
sallam
Power User
Power User


Joined: 03 Mar 2007
Posts: 65

PostPosted: Thu Jan 08, 2009 4:38 pm    Post subject: Reply with quote

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:
Code:
=IF(List.$H4="";List.D4;"")


Sorry for asking too much, as I'm only used to single line equations so far.
Back to top
View user's profile Send private message
JerzyPiotr
General User
General User


Joined: 03 Sep 2005
Posts: 32
Location: Poland

PostPosted: Thu Jan 08, 2009 9:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jan 09, 2009 12:01 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
JerzyPiotr
General User
General User


Joined: 03 Sep 2005
Posts: 32
Location: Poland

PostPosted: Fri Jan 09, 2009 1:55 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jan 09, 2009 3:00 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
sallam
Power User
Power User


Joined: 03 Mar 2007
Posts: 65

PostPosted: Tue Jan 13, 2009 2:33 pm    Post subject: Reply with quote

Code:
Filter by column G=0

Thanks very much, that did it in a very simple way.

Is it possible to make the filter execute itself automatically? or must I perform it manually every time?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group