[Solved] Deleting Consecutive empty cells

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Locked
Kprogrammer
Posts: 52
Joined: Wed Aug 22, 2018 5:47 pm

[Solved] Deleting Consecutive empty cells

Post by Kprogrammer »

There are two columns in my file . There are empty cells in column 2 in which some empty cells are consecutive.
MY requirement is:- I have to delete the entire row for each empty cell in column 2.
I wrote code for this but for consecutive empty cells it is deleting only one empty cell.This is my code below:-

Code: Select all

n=Sheet1.Columns(0).computeFunction(com.sun.star.sheet.GeneralFunction.COUNT)
 dim i
 For i = 0 to n-1
             oCell1 = Sheet1.getCellByPosition(1, i)
               if oCell1.Type = com.sun.star.table.CellContentType.EMPTY then 
                  Sheet1.rows.removeByIndex(i, 1)
               end if
next i
These are the entries in my file.
column1 column2
1 566
2
3
4 98998
5
6 787

After executing my code it shows this:-
column1 column2
1 566
3
4 98998
6 787


Ihave to delete all empty cells.
Please resolve this.
Last edited by Kprogrammer on Thu Sep 06, 2018 1:29 pm, edited 3 times in total.
Open office 3.1 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Deleting Consecutive empty cells

Post by RoryOF »

Looking at this, I think (and I haven't tried it) that when you delete a row, the next row moves up into that index position, and hence is passed. You need to reconsider your loop mechanism.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Deleting Consecutive empty cells

Post by RusselB »

Suggestion: Unless there's a need to keep the data unsorted, why not just sort the data? Doing that will put all of the empty lines at the bottom of the spreadsheet.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Deleting Consecutive empty cells

Post by FJCC »

You need to reconfigure the loop to start at the bottom and go up

Code: Select all

For i = n-1 to 0 step -1
oCell1 = Sheet1.getCellByPosition(1, i)
if oCell1.Type = com.sun.star.table.CellContentType.EMPTY then
Sheet1.rows.removeByIndex(i, 1)
end if
next i
I like the suggestion to sort the data. Do not use macros unless you have to.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Deleting Consecutive empty cells

Post by RoryOF »

There are other loop methods that might be considered - as my tutors used say "I leave these as an exercise for the student."

Sorting of the data would rearrange the order of the numbers, which might be undesirable.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Deleting Consecutive empty cells

Post by Villeroy »

Macros are unproductive bullshit. Simply use the well known keystrokes with any document in any program on any system.

Anyway, why don't you use xrange.queryEmptyCells? It returns a collection of SheetCellRanges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Deleting Consecutive empty cells

Post by MrProgrammer »

Kprogrammer wrote:I wrote code for this …
Then you should be posting in the Macros and UNO API forum.
Screen Shot 2018-08-28 at 14.19.20 .png
Screen Shot 2018-08-28 at 14.19.20 .png (17.87 KiB) Viewed 2149 times
Kprogrammer wrote:I have to delete the entire row for each empty cell in column 2.
If that is really what you need, no evil macro is needed. Filter the data, displaying only the empty cells in column 2, then select those rows and use Edit → Delete Cells. This will take about ten seconds. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know.
 Edit: Ten seconds represents how long it might take you to filter the rows and start Delete Cells. But that operation might run for a while if thousands of rows are being deleted. 
Kprogrammer wrote:Deleting consecutive empty cells
I will admit I may not understand the task since I don't know what "consecutive" has to do with "delete the entire row for each empty cell in column 2". If there's an additional requirement beyond your "My requirement is" sentence it can probably be accomplished with a formula in a third column which is then used to modify the filter specifications.
Villeroy wrote:Macros are unproductive bullshit.
Certainly true except for those people who are (A) skilled programmers and (B) familiar with the complex OpenOffice API. Hardly anyone using the forum falls into that category. Learning how to accomplish the task using the normal user interface will save most people time. Learning how to write OpenOffice macros can be fun, if that's the goal, but if the goal is to accomplish a task, macros are often a huge waste of effort.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Wed Aug 29, 2018 1:57 am, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Deleting Consecutive empty cells

Post by Lupp »

Well, I tried it. First with LibO V 6.1.1.3. Surprising result: No crash!
10000 rows with about 2500 runs of empty cells in column B.
The macro I thought would be rather efficient needed about 3 minutes.
The undo was a bit faster.
Using the Autofilter for column B in the same situation was fast with filtering, but needed also more than a minute to delete the due rows then. After undo the filter was messed up a bit.
OpenOffice did not accept GetCellRangeByName("B:B"). With the proper "correction" V4.1.5 was even a bit slower at the beginning, but seemed to work, crashed finally, however. Filter not tested.

Code: Select all

Sub deleteRows(pEvent)
If NOT buttonSingleLeftClick(pEvent) Then Exit Sub
doc0 = ThisComponent
undoMgr = doc0.UndoManager
theSheet = doc0.CurrentController.ActiveSheet
colB = theSheet.getCellRangeByName("B:B") REM "B1:B1048576" for AOO.
bEmpty = colB.QueryEmptyCells
u = bEmpty.Count - 1
undoMgr.EnterUndoContext("Remove Rows")
For j = u To 0 Step -1
  With bEmpty.RangeAddresses(j)
    theSheet.Rows.RemoveByIndex(.StartRow, .EndRow - .StartRow + 1)
  End With
Next j
undoMgr.LeaveUndoContext
End Sub

Function buttonSingleLeftClick(pEvent)
buttonSingleLeftClick = False
On Error Goto errorExit
If NOT pEvent.Source.SupportsService("com.sun.star.awt.UnoControlButton") Then Exit Function
With pEvent
  IF NOT((.Modifiers=0) AND (.ClickCount=1) AND (.Buttons=1)) Then Exit Function
  With .Source.Size
    If NOT((pEvent.X>=0) AND (pEvent.X<=.Width) AND (pEvent.Y>=0) AND (pEvent.X>=.Height)) Then Exit Function
  End With
End With
buttonSingleLeftClick = True
errorExit:
End Function
Use built-in interactive means.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Kprogrammer
Posts: 52
Joined: Wed Aug 22, 2018 5:47 pm

Re: Deleting Consecutive empty cells

Post by Kprogrammer »

FJCC wrote:You need to reconfigure the loop to start at the bottom and go up

Code: Select all

For i = n-1 to 0 step -1
oCell1 = Sheet1.getCellByPosition(1, i)
if oCell1.Type = com.sun.star.table.CellContentType.EMPTY then
Sheet1.rows.removeByIndex(i, 1)
end if
next i
I like the suggestion to sort the data. Do not use macros unless you have to.
Thanks FJCC. This code is working fine and you made it simple too.
Open office 3.1 on Windows 7
Kprogrammer
Posts: 52
Joined: Wed Aug 22, 2018 5:47 pm

Re: Deleting Consecutive empty cells

Post by Kprogrammer »

MrProgrammer wrote:
Kprogrammer wrote:I wrote code for this …
Then you should be posting in the Macros and UNO API forum.
Screen Shot 2018-08-28 at 14.19.20 .png
Kprogrammer wrote:I have to delete the entire row for each empty cell in column 2.
If that is really what you need, no evil macro is needed. Filter the data, displaying only the empty cells in column 2, then select those rows and use Edit → Delete Cells. This will take about ten seconds. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know.
 Edit: Ten seconds represents how long it might take you to filter the rows and start Delete Cells. But that operation might run for a while if thousands of rows are being deleted. 
Kprogrammer wrote:Deleting consecutive empty cells
I will admit I may not understand the task since I don't know what "consecutive" has to do with "delete the entire row for each empty cell in column 2". If there's an additional requirement beyond your "My requirement is" sentence it can probably be accomplished with a formula in a third column which is then used to modify the filter specifications.

The condition is -
If there are consecutive empty cells in a column , then delete the entire row for that empty cells.
Villeroy wrote:Macros are unproductive bullshit.
Certainly true except for those people who are (A) skilled programmers and (B) familiar with the complex OpenOffice API. Hardly anyone using the forum falls into that category. Learning how to accomplish the task using the normal user interface will save most people time. Learning how to write OpenOffice macros can be fun, if that's the goal, but if the goal is to accomplish a task, macros are often a huge waste of effort.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Open office 3.1 on Windows 7
Locked