Delete Filtered Rows

Creating a macro - Writing a Script - Using the API

Delete Filtered Rows

Postby Rajagopalanta » Fri Oct 16, 2009 2:00 pm

Hi,

I have filtered a Big Chunk of Data for a particular value in Column C, using OOBasic. Now I need to delete all the rows that are filtered... How do I go about it ???

I started by using QueryVisibleCells, but the concept of Ranges, is very confusing... Sometimes it is Array of Range... Sometimes it is an object by itself... Can someone explain the concept of Ranges to me so that I can expand my understanding.

- Raja
OOo 3.0.X on Ms Windows XP
Rajagopalanta
 
Posts: 31
Joined: Tue Jan 27, 2009 4:23 pm

Re: Delete Filtered Rows

Postby Villeroy » Fri Oct 16, 2009 2:46 pm

QueryVisibleCells() always returns one com.sun.star.sheet.SheetCellRanges collection. Even if there is only one visible range (ranges.getCount()=1) or no visible ranges (ranges.getCount()=0).
This is what MRI's macro recorder produces:
Code: Select all   Expand viewCollapse view
Sub Snippet( oInitialTarget )
  Dim oSheets As Object
  Dim oObj1 As Object
  Dim oCellRangeByName As Object
  Dim oObj2 As Object
  Dim oObj3 As Object
  Dim oObj4 As Object
  Dim oRows As Object
  Dim nCount As Long

  oSheets = oInitialTarget.Sheets
 
  oObj1 = oSheets.getByIndex( 0 )
  oCellRangeByName = oObj1.getCellRangeByName( "A1:A12" )
  oObj2 = oCellRangeByName.queryVisibleCells()
  oObj3 = oObj2.createEnumeration()
REM loop added
while oObj3.hasMoreElements()
  oObj4 = oObj3.nextElement()
  oRows = oObj4.getRows()
  nCount = oRows.getCount()
  oRows.removeByIndex( 0, nCount )
wend
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Soved] Delete Filtered Rows

Postby Rajagopalanta » Wed Oct 28, 2009 4:43 pm

Thanks villeroy... This worked very cool...

But is there any faster way to do it... Its a big chuck of data and almost every other row gets into the filter... And I could not sort the data too..

Thanks
OOo 3.0.X on Ms Windows XP
Rajagopalanta
 
Posts: 31
Joined: Tue Jan 27, 2009 4:23 pm

Re: Delete Filtered Rows

Postby Villeroy » Wed Oct 28, 2009 5:33 pm

A database can filter, sort and delete millions of rows within seconds.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Filtered Rows

Postby Rajagopalanta » Thu Oct 29, 2009 4:38 pm

Yes, I Understand. But unfornately, I will not be able to use a database here. The source of this macro is a report downloaded from old system, where in each record will be for more than 2 lines and we need to fill up some columns from the group headers too... The program mainly does the work, to bring the data into the shape to get uploaded into another system.

There are various situations of data handling which are more cumbersome to handle in database than in Spreadsheets, and this is one of them.

- Raja
OOo 3.0.X on Ms Windows XP
Rajagopalanta
 
Posts: 31
Joined: Tue Jan 27, 2009 4:23 pm

Re: Delete Filtered Rows

Postby Villeroy » Thu Oct 29, 2009 4:50 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests