[Solved] Looping through filtered results

[x2rider]

After creating a filter..

Dim oFields(#) As New com.sun.star.sheet.TableFilterField
oFilterDesc = rangeSht1.createFilterDescriptor(True)
'... add filter criteria(s) into oFields...
oFilterDesc.setFilterFields(oFields())
oFilterDesc.ContainsHeader = False
oFilterDesc.UseRegularExpressions = True
rangeSht1.filter(oFilterDesc)

What's the best way to go through the results?

If I try to user rangeSht1.Rows, I get ALL rows, not just the ones left over from filtering.
DataArray also contains ALL of the data before filtering.

So far, I've found RowDescriptions has what I need, but is there something else, or is this the way to do it?
Or am I going about this all wrong to get the data I need?

for each item in rangeSht1.RowDescriptions
rowInfo = Split(item, " ")
rowIndex = clng(rowInfo(1)) - 1

itemValue = wsSheet.getCellByPosition(colIndex, rowIndex).value
...
next

[Villeroy]

http://www.openoffice.org/api/docs/comm ... Query.html

Code: Select all

XRanges = rangeSht1.queryVisibleCells()

returns http://www.openoffice.org/api/docs/comm ... anges.html

[x2rider]

Is there not an easier way of getting the rows, or the data?
Why is there not a queryVisibleRows() ?

What I'm seeing so far, is that I get back an array of cells.

I can create an enumerator, but it goes down the first column, then goes down the next column, and so forth. This is pretty much useless.

The XRanges here does have a data property, but every element in that array is a double. Fields that are numerical are fine, but the string values are all some number (memory reference?) How do you extract the string values?

XRanges also contains a RangeAddresses array, but that requires a bit of logic as some ranges have a single entry, some have multiple, so I'd have to look at each range, use the start/end row of each one.

[Villeroy]

Get array of visible row indices from XCellRanges:

Code: Select all

Function getVisibleRows(rgs)
a() = rgs.getRangeAddresses()
Dim r()
sr = -1
for each adr in a()
	if sr  > adr.StartRow then exit for
	for i = adr.StartRow to adr.EndRow
		bas_PushArray(r(), i)
	next i
	sr = adr.StartRow
next adr
getVisibleRows = r()
End Function

My helper function PushArray for stupid Basic goes like this:

Code: Select all

REM very simple routine appending some element to an array which can be undimensioned (LBound > UBound)
Sub bas_PushArray(xArray(),vNextElement)
Dim iUB%,iLB%
	iLB = lBound(xArray())
	iUB = uBound(xArray())
	If iLB > iUB then
		iUB = iLB
		redim xArray(iLB To iUB)
	else
		iUB = iUB +1
		redim preserve xArray(iLB To iUB)
	endif
	xArray(iUB) = vNextElement
End Sub

[FJCC]

My first thought would be to use the getByIndex() method of the collection of visible cells and use the getDataArray method of each contiguous range. But it is not clear what you want to accomplish. Can you explain more about what you need to do with the data?

[x2rider]

My first thought would be to use the getByIndex() method of the collection of visible cells and use the getDataArray method of each contiguous range. But it is not clear what you want to accomplish. Can you explain more about what you need to do with the data?

This spreadsheet is used by a group of people with about 13,000 members, but it's an old organization, so about a few hundred are active at any given time I would say.

Each member has a program they run locally to track contacts they make over the radio. At the end of each active day, they export out their daily contacts, and import them into this spreadsheet. This worksheet contains all of the contacts, and from there, about 50+ scripts can be run against this data for that person to qualify for various awards.

For each award, I need to filter the data based on some criteria, then work against the results like a database. The macro then fills out various forms within the spreadsheet, and once award criteria is met, they can then apply for that particular award. The creator of the spreadsheet was a seasoned Fortran programmer, but yet didn't utilize any of the spreadsheet functions, so most macros are looping over the entire dataset and only acting on data it finds that it needs. I'm updating the spreadsheet to utilize the spreadsheet functions to speed things up. I've already improved the data loader tremendously, but each award script is now being looked at. Once someone gets over a few thousand contacts, it can really slow down the routines, so I'm identifying the routines taking a lot of time and trying to speed them up.

There is an Excel and OpenOffice version of the spreadsheet (PC and Mac as well), so as I make improvements in Excel, I then try to recreate this in the OO version.
This was much easier to do in excel.

[Villeroy]

Please, remove OpenOffice and do that in Excel!

[x2rider]

Here's the thing. Not everyone can afford Excel, therefore OpenOffice is the free alternative for the members.
OpenOffice calc can do it... and IS doing it (granted it takes longer than Excel), but I think once the routines are refined, it'll be just fine.

[Villeroy]

Many millions of other users ran into the VBA trap. I stop writing macros for anybody on this forum. I'm tired, fed up, disappointed.

[x2rider]

Many millions of other users ran into the VBA trap. I stop writing macros for anybody on this forum. I'm tired, fed up, disappointed.

Too bad you feel that way. I haven't tried your script yet, but I do appreciate you throwing that out there, it looks like what I need, you've been very helpful despite the negative rambling