Ask Your Question

Delete blank entries rows [closed]

asked 2015-11-23 14:39:21 +0100

Lion gravatar image

updated 2020-08-18 13:28:36 +0100

Alex Kemp gravatar image

I have one sheet with much data with many column I am using macro to make proper that file in last I want to filter one column which has data but not in all cell , some cells are empty, I want to filter with empty data and then remove all empty rows How can I do it with using macro code
Note : I can delete filter with data "0" but i don't know how can delete which has empty cell I have pasted my code below now any help could be appreciated

sub filter_ON()

rem define variables
dim document as object
dim dispatcher as object

rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

Dim xRange as object
Dim FilterDesc as Object
Dim FilterFields(1) as new

Dim data_da
Dim data_a

xRange = thiscomponent.getcurrentcontroller.activesheet.getCellRangeByName("D1:D20000")
FilterDesc = xRange.createFilterDescriptor(true)

FilterDesc.ContainsHeader = true
FilterFields(1).Field = 0
FilterFields(1).IsNumeric = false
FilterFields(1).Operator =
FilterFields(1).StringValue = ""



dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$D$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

dim args2(0) as new
args2(0).Name = "By"
args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfDataSel", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

This is my filter code now where need I change my code ?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-13 21:20:49.299216

1 Answer

Sort by » oldest newest most voted

answered 2015-11-24 09:31:26 +0100

pierre-yves samyn gravatar image

updated 2015-11-24 14:41:14 +0100


You can try something like (here for A1:A10 in Sheet1):

Sub DeleteEmpty

dim oRange as object, oFilter as object, document as object, dispatcher as object

oRange = thiscomponent.sheets.getByName("Sheet1").getCellRangeByname("A1:A10")
oFilter = oRange.queryEmptyCells

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

end sub


edit flag offensive delete link more


Hello Dear, Thanks for your reply It's working properly for me and It's really appreciated because you spent time for my question that's great for me again thank you :)

Lion gravatar imageLion ( 2015-11-24 13:56:22 +0100 )edit

Question Tools

1 follower


Asked: 2015-11-23 14:39:21 +0100

Seen: 4,495 times

Last updated: Nov 24 '15