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

Delete empty rows automatically
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
viking01
Newbie
Newbie


Joined: 01 Feb 2007
Posts: 4

PostPosted: Thu Feb 01, 2007 4:11 am    Post subject: Delete empty rows automatically Reply with quote

Hello
Does anyone have a way to automatically delete empty rows in a sheet?
Thanks a lot.
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Thu Feb 01, 2007 11:17 am    Post subject: Reply with quote

The best trick that I use is to insert a column, say A, then CTL-SHFT-END take you to the last cell. CTL-RightArrow to the last used row in column A. Enter the formula =Row(). CTL-C to copy. Then CTL-SHFT-UpArrow to select the entire column. CTL-V to paste to the entire column.

Leaving it selected, CTL-C again and this time CTL-SHFT-V to paste special the values (here you need to uncheck paste all and paste formula). You have now numbered col A

Now sort on an unblank column (if there isn't one single column then you will need to do the same trick in Col B with the CountA function. This moves all the blank rows to the end. Now go to the last non-blank row. Select the one below and do a CTL-SHFT-END to select the blank rows with column A set. Delete them. Now you can sort again on Row A. Now delete it. Done.

I know this sounds like a dog, but it takes about 20 secs or so once you get the hang of the cursor nav keys. And its a LOT faster than doing lots of (CTL-SHFT-DownArrow + select Row + delete Row).
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
viking01
Newbie
Newbie


Joined: 01 Feb 2007
Posts: 4

PostPosted: Fri Feb 02, 2007 1:20 am    Post subject: Reply with quote

Thanks a lot for the feedback. What I'm looking for is an automatic way (e.g. script) to delete empty rows (imagine to hand over the sheet to another person).

The sheet looks mainly like this:

Row with content


Row with content

and so on.

Thank you.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Fri Feb 02, 2007 1:39 am    Post subject: Macro to delete blank rows? Reply with quote

I suggest that you need a macro for such functionality. Perhaps search this forum for such a thing, or if you're lucky someone who knows macros (not me) will post here, or if no luck then try the macro forum.

Another option would be to have an extra column that counts how many cells contain something on each row, and then sort.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Feb 02, 2007 2:41 am    Post subject: Reply with quote

Your enquiry could be transferred to the Macros forum but I believe the fastest way to achieve your result is by sorting. Rows are never really deleted, only moved.

I am too lazy to have understood TerryE's explanation. Presumably there is one column which can serve as an index. In other words, if, say, the cell in column A is blank, does that mean the whole row is blank?

All you need to do is select A1 (or another starting point if preferred). Hit Shift+Ctrl+End. That will select the entire used area, including empty intervening rows. Use the menu Data >Sort. Sort by your chosen column. Make sure options on the second tab of the dialogue are correct. Then sort. Empty rows are no longer within the used area.

Send me a pm if you want the thread transferred.
_________________
search forum by month
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Fri Feb 02, 2007 5:16 am    Post subject: Reply with quote

Sorry I just explained how and not what or why. As noranthon says, the easiest way to get rid of blank rows is to move them to the bottom of the spreadsheet. You can do this by sorting on a column where the cells for the rows that you want to move are blank.

The problem is that this leaves you with the rows in the wrong order. If you need to put them back into the right order then you need a [temporary] column which contains the row number in it. What my key sequence does is to add this which the minimum no of key presses. If you do a simple worked example you will see how it works.

Finally yes, this is simple to programme, but if you know how to use basic scripting there a loads of variations that you can apply depending on whether code simplicity or minimising runtime is your primary goal.
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Feb 02, 2007 7:03 pm    Post subject: Reply with quote

Since we seem to be discussing scripting, TerryE, what would be most efficient - automating a sort (my guess) or finding and deleting (one by one) empty rows?

viking01 may be able to record a macro which he can use to sort in such situations. An ordinary recording will probably omit crucial details, which means someone writing the script.

This seems to be a good time to mention again the alternative macro recorder. To save time:
Quote:
There is a replacement macro recorder available as an extension here:
http://www.paolo-mantovani.org/downloads/DispatchToApiRecorder/

Paolo Mantovani in Jan.07 wrote:
You need also to install at least one "transformer" (currently there is only a Calc Transformer). The transformer is also an extension and is the component that actually makes the Dispatch-to-API translation of the macro code.

After the installation, you do not need to know anything else: reboot openoffice and just use the macro recorder as usual.

If you want to switch back to the "classic" macro recorder you have only to remove or disable both extensions from the extension manager dialog.

_________________
search forum by month
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Fri Feb 02, 2007 11:40 pm    Post subject: Reply with quote

Terry, if I were programming this in Basic, or any other scripting language, then I probably wouldn't start with a sort. I'd use the queryEmptyCells() method of the XCellRangesQuery interface on a selected column range. This returns a XSheetCellRanges collection which I could then traverse with a For loop since this supports XEnumerationAccess. I'd also enumerate it backwards from the last cell to the first -- this makes the deletion process easier to track. I'd loop around this picking up the row details and trashing the rows. This gives a run time of order the number of rows to be deleted.

The sort approach might be faster if the worksheet was sparse. Coding this is an exercise for the reader Smile -- My time at the moment is getting to bottom of this mega memory leak that we've found in the basic RTE.
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Feb 03, 2007 7:05 am    Post subject: Reply with quote

Having your data in A2:F100 below headers in A1:F1, add a new column with label "Blanks" in G1 and formula
G2: =COUNTA(A2:F2)
Data>Filter>Standard Filter...
Blanks = 0
"More Options", "Copy Results"
You should get a copy of all rows having one entry at least.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Sat Feb 03, 2007 2:17 pm    Post subject: Reply with quote

yup, agreed, but I have found the Data->Fillter optoin a lot more flakey that the Excel Data->Filer->Autofilter. Also you then need to paste all into another sheet, and then paste formatting of the rows from the previous sheet into the new; delete old and rename the new to old. As they say in arabic -- nus wa nus. //T
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Feb 03, 2007 2:53 pm    Post subject: Reply with quote

TerryE wrote:
yup, agreed, but I have found the Data->Fillter optoin a lot more flakey that the Excel Data->Filer->Autofilter. Also you then need to paste all into another sheet, and then paste formatting of the rows from the previous sheet into the new; delete old and rename the new to old. As they say in arabic -- nus wa nus. //T


No, forget about Auto-Filter. Use the standard one with move-option. It copies with formattings. You can copy to the right side within the same sheet. Then remove the old columns or just keep on working with the old ones and call Data>Refresh in order to apply the same filter to the same output range, automatic resizing inclusive. Unfortunately there is no option to copy values only. So you need to be aware of your references. Former B10 =$A10 will refer to $A8 after copying to some cell in row 8. However B10 =A10, copied to Z8 keeps referring to it's left neighbour Y8. Stopping auto-calculation won't help, because filtering triggers recalculation.
A badly designed sheet may have all kinds of relative/mixed/absolute refs, leading to wrong results caused by simple copy and paste.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
viking01
Newbie
Newbie


Joined: 01 Feb 2007
Posts: 4

PostPosted: Wed Feb 07, 2007 4:32 am    Post subject: Reply with quote

Thanks to all for the support.
Noranthon, could you please transfer this post to the macro forum. The main thing is that I try to find a solution to automatically delete all the empty rows without any further action from a user.

Thanks a lot.
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Thu Feb 08, 2007 7:42 am    Post subject: Reply with quote

I think that you will have performance issues and architectural issues if you try to delete blank rows without any user interaction. Better that you use a template which adds a button and/or a menu command to delete empty rows. This is pretty straight forward and you could use this as a good exercise to get into OOo Basic and the UNO models for Calc.

Once you've got this working, then you can get into event handling and the architecture that you would need to implement what you are seeking. I'll give you an example of the sort of issue that will catch you out: how will you be able to insert a row, because <row insert><select cell><enter data> becomes <row insert><automatic delete of blank row> ... ?
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
z300xy
Newbie
Newbie


Joined: 10 Jul 2007
Posts: 4

PostPosted: Tue Jul 10, 2007 9:44 pm    Post subject: Reply with quote

I'm new to OOo and this forum and couldn't help but wonder why a similar CTRL+G (Goto Special) excel command hasn't been added to OpenOffice? I use the CTRL+G command all the time to select all the blanks within a given range. Once these blank cells and/or rows have been selected I can then delete them with a right mouse click and clicking delete.

Blanks aren't the only use for this command, but it seems to the topic of this thread. Anyone who can show us excel folks how to do the same thing in OpenOffice would be greatly appreciated!
Back to top
View user's profile Send private message
z300xy
Newbie
Newbie


Joined: 10 Jul 2007
Posts: 4

PostPosted: Tue Jul 10, 2007 10:22 pm    Post subject: Re: Delete empty rows automatically Reply with quote

viking01 wrote:
Hello
Does anyone have a way to automatically delete empty rows in a sheet?
Thanks a lot.


It took me a little while but I found an old post from 2005 that does what we need viking01. I'm personally shocked why this hasn't been incorporated into OpenOffice under the CTRL+G or CTRL+F commands. Nevertheless, until OpenOffice catches up with excel in this area this will have to due.

http://www.oooforum.org/forum/viewtopic.phtml?t=23168&highlight=goto+special+blank

And most importatnly special thanks goes out to Ian Laurenson who created the macro back in 2004!

Peace Out Brothers & Sisters!
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
Goto page 1, 2  Next
Page 1 of 2

 
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