Delete Filtered Rows

[Rajagopalanta]

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

[Villeroy]

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

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

[Rajagopalanta]

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

[Villeroy]

A database can filter, sort and delete millions of rows within seconds.

[Rajagopalanta]

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

[Villeroy]

If there are formulas, turn off auto-calculation. Lock the controllers.
http://api.openoffice.org/docs/common/r ... ontrollers
http://api.openoffice.org/docs/common/r ... alculation