Page 1 of 1
[Solved] Delete Empty Rows using a macro
Posted: Mon Sep 09, 2013 10:23 am
by 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?
----------------------------------
Re: Delete Empty Rows using a macro.
Posted: Mon Sep 09, 2013 10:36 am
by RoryOF
Why not simply sort the table? It is easier to write the word "Macro" than it is to write a macro.
Re: Delete Empty Rows using a macro.
Posted: Mon Sep 09, 2013 1:25 pm
by 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...
Re: Delete Empty Rows using a macro.
Posted: Tue Sep 10, 2013 9:45 pm
by 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
Re: Delete Empty Rows using a macro.
Posted: Fri Jan 31, 2014 10:01 pm
by 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.
Re: [Solved] Delete Empty Rows using a macro
Posted: Wed Jul 31, 2019 2:56 pm
by 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.
Re: [Solved] Delete Empty Rows using a macro
Posted: Wed Jul 31, 2019 3:49 pm
by Villeroy
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.
Re: [Solved] Delete Empty Rows using a macro
Posted: Thu Aug 01, 2019 8:43 am
by CNR
Thanks, but it has to happen with a push of a button and "Record Macro" doesn't replicate all the actions i make.
Re: [Solved] Delete Empty Rows using a macro
Posted: Thu Aug 01, 2019 11:57 am
by 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
Re: [Solved] Delete Empty Rows using a macro
Posted: Thu Aug 01, 2019 12:14 pm
by CNR
You are the boss !!
Thanks a lot !