[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
sallam
Power User

Joined: 03 Mar 2007
Posts: 65

 Posted: Thu Jan 01, 2009 4:50 am    Post subject: how to hide rows if specific cells are empty 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

 Posted: Tue Jan 06, 2009 10:11 am    Post subject: help please..
Xelebes3
Power User

Joined: 30 Nov 2008
Posts: 50

 Posted: Tue Jan 06, 2009 10:46 am    Post subject: You will need tables. Use Base.
JerzyPiotr
General User

Joined: 03 Sep 2005
Posts: 32
Location: Poland

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)
 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
sallam
Power User

Joined: 03 Mar 2007
Posts: 65

Posted: Thu Jan 08, 2009 4:38 pm    Post subject:

Thanks very much Jerzy..
I need some details here please:
- do I type '=' before I select a range of cells, then paste your code after 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.
JerzyPiotr
General User

Joined: 03 Sep 2005
Posts: 32
Location: Poland

 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
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 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 https://forum.openoffice.org
JerzyPiotr
General User

Joined: 03 Sep 2005
Posts: 32
Location: Poland

 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
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 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 https://forum.openoffice.org
sallam
Power User

Joined: 03 Mar 2007
Posts: 65

Posted: Tue Jan 13, 2009 2:33 pm    Post subject:

 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?
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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