Calc Basic - Access filtered data from macro code

Today I stood into another problem while helping in the creating of a Calc App powered by Basic macros.

The thing is, I’m using calc as a database and I have a sheet where I save all the input data as if it was a database table, and here lies the problem: I search row by row “manually” to find the row from where I get the rest of the data saved, but it is painfully slow, so I though using Calc Filters to speed up the search to only filtered data. Making the filters themselves is relatively “easy” but accessing to that filtered data on the macro is getting really tedious.

I have been reading the helping document called CalcAsASimpleDatabase and in page 17 it mentions how to copy the filtered data to another part of the document, but it never mentions how to just keep the Cell Range as a variable from which I can then rescue the data I need to show in another place.

Here is a simplified version of the db-like sheet I’m using as table for storing the data:
Simplified table with autofilters

And here the filtered data I would like to rescue from it:

Filtered Data I would like to access in basic

That said, I would rescue more than 1 row of data in the real one, but for easiness here is just 1 row.

Thanks in advance for the help, I hope I have been clear, if not please let me know.

==== EDIT ====

First, here’s a reinterpretation of the simplified version of what I’m making, which should make it easier to understand what I’m trying to achieve:

The code then read the “Form” Year, Month and Zone and uses functions to fill first the users data and days making a fillable calendar of some sort, which then is filled with data that was already saved, if it existed that is. With the info we have in Data, it would look like this:

Form sheet after loading data from Data sheet

As @mikekaganski suggested, here’s the code that fills the data for each user (the user list is first generated from another sheet using another function) and that I’m pretty sure isn’t optimized at all, it uses other functions and the macro should have globals which actually doesn’t have, but at least gives a vision of how I’m loading the data right now, fullfilling Mike’s suggestion:

Sub fillSavedData(month as string, year as string,zone as string)
dim origin,destiny, southzone as string
dim i,j,k,n,col, row, endrow,int,startrow,startcol, endcol, totrows as integer
'Where we are going to start filling data in the sheet
'Row numbers in Form sheet
do while isLastRegister(0,i,0)=false 'sheet, row, column; return true if the cell has data or false if not
'we go through the Data sheet
endcol=daysOnMonth(month,year) 'month as String, year as Integer; get's a month name and a year and return an Integer with the number of days it has

for i=1 to nrows(3,0,0)+1
	if zone="South" then 'North is always North, but South can be South I or South II
		'search PID in user sheet and write the exact zone
		'k form counter, i for registers, n for personal data
		for k=startrow to endrow
				if obtainValue(3,i,0)=obtainValue(0,k,0) then
					for n=0 to totrows+1
						if obtainValue(0,k,0)=obtainValue(2,n,2) then	
						end if		
				end if
	end if
	if mes=obtainValue(3,i,6) and year=obtainValue(3,i,5) and southzone=obtainValue(3,i,2) then
		for j=startrow to endrow
			if obtainValue(3,i,0)=obtainValue(0,j,0) then
			end if
		if int=obtainValue(3,i,7) then
		end if
	end if
end sub

Here is the .ods of the simplified example I created, as code highlight here doesn’t exist

Besides asking for help with a specific problem on your way to the solution you imagined, it would also help to post initial solution that was “painfully slow”, to see if someone would come with an advise how to improve that one without using UI filtering (which is simply rows with height=0).

Thanks for the fast response. Well my plan was to use sheet filters described on the document linked above, yet I don’t know if they differ or are the same as UI Filters, just used them for a fast and easy way to represent what I want to achieve.

The code itself is in Spanish, and depends on other handmade functions, I will try my best to translate and correctly comment it prior posting it.

Again thanks for your time!

Kind of advice in advance:
If you are decisive about working with custom code there are three things you should know:
-1- If you visually (UI) select a filtered range, the ThisComponent.CurrentSelection will only contain the subranges not filtered away (also invisible then).
-2- If you create a object (by mySheet.GetCellRangeByName() e.g.) it has a .Rows property giving acces to each row intersecting the range. Each row object has a Boolean property .IsFiltered having the Value True for rows “filtered” away and False otherwise.
-3- Rows hidden without using a filter (by direct user action e.g.) are not set to .IsFiltered=True. They also have their original .Height settings, but are set to IsVisisble=Fale.

I don’t know a comfortable way to just get unfiltered rows.
There is a CellRange method to get the visible parts: theRange.QueryVisibleCells().
If you can assure that there are no rows hidden though not filtered, you may do something like demonstrated in the attached example.
(Attachment updated 2018-10-19 15:22 CEST)

See the new demo announced in my recent comment.

Please report if you find a better way.

Thanks a lot for the advices! Yeah that aproaches to what I want to achieve, but I guess there is no way to get only visible (IsFiltered=False) rows without checking each row from a selected range isn’t it? That’s what I’m trying to achieve so I have a CellRange with only 50 rows instead of 5000 rows.

Quoting @XanderN: “…from a selected range…”
Here applies what I wrote under -1-: If the selection actually intersects at least one filtered row you don’t get a ShetCellRange object as the CurrentSelection, but a SheetCellRanges (Regard the plural!) object. The ranges accessible this way no longer have intersections with filtered rows. You don’t need to inspect every single row, but of course, the treatment of an arbitrary number of ranges is somehow more complicated.

My bad for not specifying how I select the range, I meant to select the range using Sheet(x).getCellRangeByPosition(a, b, c, d) in basic code, instead of UI selecting as macro recorder does

Well, such a range is not selected at all.
See also the amendment to my answer.

The data in the sheet will be only touched by macros, so it would be safe to filter as in your admendment example.

I will try filtering the sheet by macro, then using range.QueryVisibleCells() to reduce the search’s range, and deleting the filter before the end so I can create new filters for new searchs and post my findings here.

Thanks a lot for the time and dedication put into giving a factible solution to what I was looking for!

If you also do the filtering exclusively by macros I would suggest you don’t let the filter hide rows.
Set it up instead to write the resulting data to a dedicated range, best in a different sheet.
If you want to work on the data in addition, you get them by myDataArray = targetRange.GetDataArray
See Sub filterAndCopyResults contained in the new demo.

I think you probably don’t want to hear this, but may I be the first to gently suggest that you consider moving to a database.

In a database you can have indexes to search for things. This makes searching blindingly fast.

While it’s true that a hammer can solve almost any problem, if you’re trying to turn in a screw, a screw driver is really what you should be using.

To help you get started I put this page together. So far it has over 7000 views.

Please consider this direction. I think in the long run you’ll be much happier.

I first considered to make an “app” that made use of Calc and Base, as Spreadsheets were needed for some operations, but after looking into Base I decided the interoperability between Base and Calc wasn’t as good as needed, and seeing that native Base DB isn’t multi-user, I deprecated the idea, although it would be ideal for cases like the one I’m trying to resolve here. In any case, I’m a total newbie at Base, so correct me if I’m wrong and thanks for the suggestion.

I am a Kentuckian who is not afraid to use duct tape, bailing wire, and nine ‘pound hammers to fix things. This is my nine pound hammer approach for handling filtered cells in a spreadsheet. The secret command for handling hidden cells is
in LO visual basic is "orow.isVisible".

Here is a code snippet using this feature:

 Function ReadFilteredCells
'My nine pound hammer approach for selecting only filtered cells in a spreadsheet.
'Declare variables
dim oRow as object
dim oSheet as object
dim readVar as string
dim title as string
dim variable as string
'Open the sheet to process.
oSheet = thiscomponent.getcurrentcontroller.activesheet
'In my case, I have sorted my data so that all of my visible cells are contiguous. 
'Define the first row number where the data begins. In my case, row 0 is the header of the sheet
'so I start with row 1.
rowNum = 1
'Spin through all of the non-blank data.
	readVar = osheet.getCellByPosition(0,rowNum).getString
	if trim(readVar) = "" then exit do 'Done!!! - bail out of the do loop
	oRow = osheet.Rows.getByIndex(rowNum)
	if orow.isVisible  then 'We're to the visible rows
		'Do whatever operations you wish to do with the visible rows here :-)
		'In this example, I'm printing the first visible and quitting
		title = "The first visible row number is " + rowNum
		variable = "The contents in the first visible cell is "+_
		msgbox variable,0,title
		exit do
	end if
	rowNum = rowNum + 1 'Keep spinning through the sheet
end function

Did you read my statement concerning the difference between NOT orow.IsVisible and orow.IsFiltered?
(True value of orow.IsFiltered means the “negative case”: The row is not included with the matching records.)