Delete blank entries rows

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(“com.sun.star.frame.DispatchHelper”)

Dim xRange as object

Dim FilterDesc as Object

Dim FilterFields(1) as new com.sun.star.sheet.TableFilterField



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 = com.sun.star.sheet.FilterOperator.EQUAL

FilterFields(1).StringValue = “”

FilterDesc.SetFilterFields(FilterFields)

xRange.Filter(FilterDesc)

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = “ToPoint”

args1(0).Value = “$D$2”

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

dim args2(0) as new com.sun.star.beans.PropertyValue

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 ?

Hi

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
thiscomponent.currentController.select(oFilter)

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

end sub

Regards

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 :slight_smile: