Ask Your Question
0

Delete blank entries rows [closed]

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

Lion 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("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 ?

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
1

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

pierre-yves samyn gravatar image

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

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

edit flag offensive delete link more

Comments

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 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 3,645 times

Last updated: Nov 24 '15