[Solved] Delete Empty Rows using a macro
[Solved] Delete Empty Rows using a macro
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?
----------------------------------
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].
Reason: tagged [Solved].
openoffice 4.0 on Windows 8
Re: Delete Empty Rows using a macro.
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.15 on Xubuntu 22.04.4 LTS
Re: Delete Empty Rows using a macro.
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??
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...
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
Last edited by RoryOF on Mon Sep 09, 2013 1:35 pm, edited 1 time in total.
Reason: Added code tags (RoryOF, Moderator)
Reason: Added code tags (RoryOF, Moderator)
openoffice 4.0 on Windows 8
Re: Delete Empty Rows using a macro.
Hi,
this code should do the Job on Sheet1:
Here's a sample:
R
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
R
- Attachments
-
- Delete_empty_rows.ods
- (35.49 KiB) Downloaded 595 times
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Delete Empty Rows using a macro.
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.
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
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.
i.e.: to delete all rows where colon "A" cell is empty, from A16 to A39.
Openoffice 4.1.6 - Windows 10Pro
Re: [Solved] Delete Empty Rows using a macro
Very easy without macro.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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Delete Empty Rows using a macro
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
Re: [Solved] Delete Empty Rows using a macro
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Delete Empty Rows using a macro
You are the boss !!
Thanks a lot !
Thanks a lot !
Openoffice 4.1.6 - Windows 10Pro