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(“”)

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 ?


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


1 Like

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: