Ask Your Question
0

How to create macro that deletes a row under these criteria?

asked 2016-12-16 14:21:42 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hello,

I need a macro that would delete all rows if these two criteria are met:

  • a cell in D column has a value equal to 5
  • a cell in H column is blank

I would be grateful for any help on this.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-12-17 19:55:57 +0200

pierre-yves samyn gravatar image

updated 2016-12-18 06:54:21 +0200

Hi

For example (work for the active sheet):

sub DeleteRows()

dim oSheet as object
dim oCellCursor as object, oCellRangeAddress as object, oCell as object
dim lRowNumber as long, lEndRow as long

oSheet = thiscomponent.currentController.activesheet
oCellCursor = oSheet.createCursor()

' Find the last used row '

oCellCursor.gotoEndOfUsedArea(True)
oCellRangeAddress = oCellCursor.getRangeAddress()
lEndRow = oCellRangeAddress.EndRow

'from the bottom to the top, one row at a time '

for lRowNumber  = lEndRow to 1 step -1
'   if cell in col D = 5 '
    oCell = oSheet.getCellByPosition(3, lRowNumber)
    if oCell.value = 5 then

'   if cell in col H = empty '      
        oCell = oSheet.getCellByPosition(7, lRowNumber)
        if oCell.Type = com.sun.star.table.CellContentType.EMPTY then
            oSheet.rows.removeByIndex(lRowNumber, 1)
        end if
    end if  
next lRowNumber  

print "done"

end sub

[EDIT] I remind you that you can do this without macro with a simple standard filter to select the lines then right click on the line header to remove them...

Regards

edit flag offensive delete link more

Comments

Many thanks! (Actually, I need this as a part of another macro that I have already created. A filter would require the additional manual work)

musha9 gravatar imagemusha9 ( 2016-12-19 12:09:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-16 14:21:42 +0200

Seen: 1,331 times

Last updated: Dec 18 '16