[Solved] Delete Empty Rows using a macro

[nawespet]

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?
----------------------------------

[RoryOF]

Why not simply sort the table? It is easier to write the word "Macro" than it is to write a macro.

[nawespet]

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

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...

[F3K Total]

Hi,
this code should do the Job on Sheet1:

Code: Select all

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

[kytas]

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.

[CNR]

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.

[Villeroy]

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.

[CNR]

Thanks, but it has to happen with a push of a button and "Record Macro" doesn't replicate all the actions i make.

[Villeroy]

Code: Select all

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

[CNR]

You are the boss !!

Thanks a lot !