[Solved] Delete Empty Rows using a macro

Creating a macro - Writing a Script - Using the API

[Solved] Delete Empty Rows using a macro

Postby nawespet » Mon Sep 09, 2013 10:23 am

I was wondering how to delete empty rows in a table.

I am making a table that holds a various number of employees, with name, birthdate, ect.

I have a place on top of the table where the person enters the details of the employee then when I press the button the information is placed in the last row of the column.

I want to make a macro that will select the entire table and delete the empty rows between the existing employees and the new employee. As it is unknown to how many employees will be in the table I need to find a way to filter out the empty rows.

Example:
john | smith - row above table
-------------------------------------------------------------------
jack | john
-------------------------------------------------------------------------- -- existing employees.
jessica | williams
------------------------------------------------------------------------------
------------------------------------------------------------------------------- blank rows in my table.
--------------------------------------------------------------------------------
john | Smith Entered in John Smith in the bottom row. How do i delete the top blank rows?
----------------------------------
Last edited by Hagar Delest on Fri Jan 31, 2014 11:11 pm, edited 1 time in total.
Reason: tagged [Solved].
openoffice 4.0 on Windows 8
nawespet
 
Posts: 4
Joined: Mon Sep 09, 2013 10:14 am

Re: Delete Empty Rows using a macro.

Postby RoryOF » Mon Sep 09, 2013 10:36 am

Why not simply sort the table? It is easier to write the word "Macro" than it is to write a macro.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29569
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Delete Empty Rows using a macro.

Postby nawespet » Mon Sep 09, 2013 1:25 pm

Because I have a column next to the employees that has a specific ID. If I sort the employee's then their ID would change...
Also I found a solution but its not working, is there anything wrong with this??

Code: Select all   Expand viewCollapse view
Sub remove_empty()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc     ' Filter descriptor.

  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
 
  oSheet = ThisComponent.getSheets().getByName("Employee List")
  oRange = oSheet.getCellRangeByName("B9:T109") 'Set the range on employee's.
 
  oFilterDesc = oRange.createFilterDescriptor(True)
 
  With oFields(0)   
    .Field = 3            ' Filter column D.
    .Operator = com.sun.star.sheet.FilterOperator.EMPTY 
  End With
 
  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = False
  oSheet.filter(oFilterDesc)
 
 
End Sub


Because when I use it everything except the rows with employees in them is displayed, but that I mean every other row in the entire spreadsheet becomes hidden... I have tried changing .Operator = com.sun.star.sheet.FilterOperator.EMPTY to NOT_EMPTY but it has the same result...
Last edited by RoryOF on Mon Sep 09, 2013 1:35 pm, edited 1 time in total.
Reason: Added code tags (RoryOF, Moderator)
openoffice 4.0 on Windows 8
nawespet
 
Posts: 4
Joined: Mon Sep 09, 2013 10:14 am

Re: Delete Empty Rows using a macro.

Postby F3K Total » Tue Sep 10, 2013 9:45 pm

Hi,
this code should do the Job on Sheet1:
Code: Select all   Expand viewCollapse view
Sub S_delete_empty_rows
    dim bEmpty as boolean
    dim nCounter as integer
    oSheet = thiscomponent.sheets.getbyname("Sheet1")
    oCursor = oSheet.createcursor
    oCursor.gotoendofusedarea(false)
    nEndColumn = oCursor.rangeaddress.EndColumn
    nEndrow = oCursor.rangeaddress.Endrow
    nCounter = 0
    for i = nEndrow to 0 step - 1
        bEmpty = true
        for k = 0 to nEndColumn
           ocell = osheet.getcellbyposition(k,i)
           if oCell.Type <> com.sun.star.table.CellContentType.EMPTY then
           bEmpty = false
           exit for
           end if
        next k
        if bEmpty then
            osheet.rows.removeByIndex(i,1)
            nCounter = ncounter + 1
        end if
    next i
    msgbox (nCounter & " rows deleted",64,"Success")           
End Sub

Here's a sample:

R
Attachments
Delete_empty_rows.ods
(35.49 KiB) Downloaded 325 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 920
Joined: Fri Dec 16, 2011 8:20 pm

Re: Delete Empty Rows using a macro.

Postby kytas » Fri Jan 31, 2014 10:01 pm

Hi,
this macro is working fine. but i have problem, cell is empty , i have in it command if. is it possible somehow change the code to prevent of this problem ?
thank you for answer.
kytas
 
Posts: 1
Joined: Fri Jan 31, 2014 9:56 pm

Re: [Solved] Delete Empty Rows using a macro

Postby CNR » Wed Jul 31, 2019 2:56 pm

I know this is an old thread but does anyone know in this code how can you define row range to A16:A39 ?
i.e.: to delete all rows where colon "A" cell is empty, from A16 to A39.
Openoffice 4.1.6 - Windows 10Pro
CNR
 
Posts: 7
Joined: Fri Jul 19, 2019 10:30 am

Re: [Solved] Delete Empty Rows using a macro

Postby Villeroy » Wed Jul 31, 2019 3:49 pm

CNR wrote:I know this is an old thread but does anyone know in this code how can you define row range to A16:A39 ?
i.e.: to delete all rows where colon "A" cell is empty, from A16 to A39.

Very easy without macro.
menu:Filter>Standard Filter...
<Column A> <-- is empty -->
Then delete entire rows and remove the filter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27215
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete Empty Rows using a macro

Postby CNR » Thu Aug 01, 2019 8:43 am

Thanks, but it has to happen with a push of a button and "Record Macro" doesn't replicate all the actions i make.
Openoffice 4.1.6 - Windows 10Pro
CNR
 
Posts: 7
Joined: Fri Jul 19, 2019 10:30 am

Re: [Solved] Delete Empty Rows using a macro

Postby Villeroy » Thu Aug 01, 2019 11:57 am

Code: Select all   Expand viewCollapse view
Sub Main
REM Delete rows from active sheet's used range
REM where the value in the used range's first column is blank
   sh = ThisComponent.CurrentController.getActiveSheet()
   urg = getUsedRange(sh)
   addr = urg.getRangeAddress()
   addr.EndColumn = addr.StartColumn
   col1 = urg.queryIntersection(addr)
   oBlanks = col1.queryEmptyCells()
   for i = oBlanks.getCount() -1 to 0 step -1
      rg = oBlanks.getByIndex(i)
      rows = rg.getRows()
      rows.removeByIndex(0, rows.getCount())
   next
End Sub

Function getUsedRange(oSheet)
REM rectangle of cells having content
Dim oCursor
   oCursor = oSheet.createCursor()
   oCursor.gotoStartOfUsedArea(False)
   oCursor.gotoEndOfUsedArea(True)
   getUsedRange = oCursor
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27215
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Delete Empty Rows using a macro

Postby CNR » Thu Aug 01, 2019 12:14 pm

You are the boss !!

Thanks a lot !
Openoffice 4.1.6 - Windows 10Pro
CNR
 
Posts: 7
Joined: Fri Jul 19, 2019 10:30 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests