Calc macro to hide multiple rows based on a cell's value?

English
#27

@mikekaganski I asked a specific, detailed, clear, question for a VBA code to be made work for Libre Calc. I tried other suggestions. They didn’t work or were difficult to get to work or wouldn’t work because they changed a bunch of things that cannot change in the project file. Why is that so difficult for you to understand?

It’s quite showing how disgusting this community really is when a developer/moderator who should be a role model to the community is immaturely taking whacks at a user that asked a question like a kid in grade school. FFS grow the hell up!

#28

@Bort You are the one here who shows a strange immature behavior!

#29

@karolus So when a person asks a specific, detailed, clear question of what they want and they’re given things that don’t answer the question and aren’t a solution because they don’t work, rants insulting the software that don’t even attempt answering the question, insults on their character (that continues, now by multiple users), and politely says it doesn’t work for them that’s immature behavior but the ranting and carrying on and insults are completely okay.

What planet are you from???

#30

This carrying on with this, I got a solution that works several hours ago, is utter and complete nonsense! And I’m immature??? LMFAO!!!

Thank god for an ignore feature!

#31

I’m not a native speaker, but I’m not sure that the word “disgusting” should be used so often in written speech. Correct me if I’m wrong.
Note: LMFAO means “Хохочу до упаду, черт возьми”.

#32

And now its beyond your capabilities to do anything on yourself in this trivial task?? :sob:

#2

I wrote this Python-code and it (hopyfully) does exactly what you want if you bind it to sheet-event Content Changed

def change_on_country(event):
    if event.AbsoluteName.endswith("$C$5"):
        sheet = event.Spreadsheet
        get_range = sheet.getCellRangeByName
        country = event.String
        india,canada = map(get_range, ("A7:A18","A19:A25"))
        
        if country in ('India','Canada'):            
            india.Rows.IsVisible, canada.Rows.IsVisible = country=="India",  country=="Canada"
        else:
            india.Rows.IsVisible, canada.Rows.IsVisible = True, True
#3

Thanks! Do I have to do anything special with the macro libraries or anything to use phython code?
And how would I add more ranges and key words? There are 12 selections in the list box that would have to hide random ranges of rows, it’s not just 2 ranges from 2 selections.

For example if 1 is selected rows 200:225 have to be hidden.
if 2 is selected I’d like it to hide rows 4:18, 34:97, and 104:169.
I may be able to work with python easier than calc basic.
I’ll play around and see if I can figure that out. Thanks!!

#4

may I recommend install APSO.oxt from here and use it for Management of Python-scripts for Libreoffice.

Additionally … you need to bind the function above to SheetEvent Content changed
(→Right-click on SheetTab→Sheetevents… )

#5

I’ve been out of the practice of coding for over a decade. I’m not good with it anymore and I don’t have a clue how to use those tools. I’ll give it a try but I’m not confident I can get any of it to work. I have no idea how to read a debugger. No clue how to even install it.

Testing your python script I used the code you wrote to replace a recorded macro. It’s throwing a syntax error asking for “then” constantly. I can’t do anything in the document or the code because of the error.

Nope! I’m not able to work with python scrips. Saw the wiki here and that’s way too much digging and editing etc just for one macro. I also don’t have a python editor and have no clue how to write python code at all. If an editor is needed other than a text editor that’s another thing I don’t have.

Thank you very much! I’m not able to use that though.

#6

python is not Basic, why do you expect it works by magic within BasicIDE executed by Basic-Interpreter??

#7

LOL I don’t expect it to work like magic. I gave it a shot the way I know how.
Python’s not for me. I’ve just barely started getting a slight but still baffled beyond confused understanding of Basic.

#9

I had already suggested you to install APSO. It does most of the stuff behind the scene, but in any case you need to write code yourself (either in Basic or Python) or to find some guy who write it for you.

#10

Coding takes forever to learn and even longer to get good at. I’m old. I just want to get this sheet working.

#12 
Sub hideRows
	dim oDoc as object, oSheet as object, oCell as object, p(), s$, oRange as object, oCur as object, i&, j&, a1(), a2()
	
	rem your data
	const cCell="D1" 'name of the decision cell
	p=array( 	_
					array("Bohemia", "3:7"), _
					array("Canada", "22:25", "30:38", "48:52"), _
					array("India", "17:21", "60:61") _
					)


	oDoc=ThisComponent
	
		rem ********** if you have many formulas in your sheet, then this can be faster
		dim bAutomatic as boolean
		bAutomatic=oDoc.isAutomaticCalculationEnabled 'value of the: Data/ Calculate/ AutoCalculate
		if bAutomatic then oDoc.enableAutomaticCalculation(false) 'deactivate AutoCalculate
		oDoc.lockControllers() 'no screen actualization
		oDoc.addActionLock() 'lock
					
	oSheet=oDoc.CurrentController.ActiveSheet 'active sheet
	oCell=oSheet.getCellRangeByName(cCell) 'decision cell
	s=oCell.string 'string from decision cell
	
	rem view all rows
	oCur=oSheet.createCursor
	oCur.goToEnd(false)
	oRange=oSheet.getCellRangeByPosition(0, 0, 0, oCur.RangeAddress.EndRow) 'used rows in column A
	oRange.Rows.isVisible=true 'set all rows to visible
	
	rem hide rows
	for i=lbound(p) to ubound(p) 'loop all arrays
		a1=p(i)
		if a1(0)=s then 'array with needy name
			for j=1 to ubound(a1) 'loop all ranges in array
				a2=split(a1(j), ":")
				oRange=oSheet.getCellRangeByPosition(0, CLng(a2(0))-1, 0, CLng(a2(1))-1 ) 'rows in column A
				oRange.Rows.isVisible=false 'hide the rows in actual range
			next j
			exit for 'exit the loop
		end if
	next i

		rem **********
		oDoc.removeActionLock() 'no lock
		oDoc.unlockControllers() 'screen actualization
		oDoc.enableAutomaticCalculation(bAutomatic) 'set AutoCalculate to the original value
End Sub

Example
hideRows.ods (13.8 kB)

Jak skrýt prázdné řádky?
#17

Perfect! Words can’t express Thank you enough!

#13

The built-in filters work perfectly well. Instead of playing around you could upload an example and tell us what goes wrong when you apply advanced filtering.
Even if you insist in writing macros for the wrong tool (again: all this is database stuff), you could write a macro that controls the advanced filter like this may be: https://forum.openoffice.org/en/forum/download/file.php?id=136 which makes use of some generic code working with a named criteria range and a named database range. The input goes into a form control which triggers the macro with every character you add or remove.

#16

This 700 MB software is prepared to be usable without writing stupid macro programs. All this works more or less exactly as it used to work in Microsoft Excel since the early 90ies.

Preparing advanced filtering with no macro code.

  1. Define a database range. This is a special type of named range for database-like lists to be filtered and sorted. It “remembers” the last sort and filter criteria. Again, true database tables are preferable.
  2. Add a criteria range as descirbed in the help files and in countless example files. The most simple criteria range consists of 2 cells: a column label specifying a distinct column of a database range and one cell below specifying one criterion.
  3. [Optional] Name the 2 cells and flag the named range as “filter criteria”…
  4. Call the auto-filter dialog and point to the criteria range. If you executed step 3, you can pick the criteria range from a list box.
  5. After clicking OK, the database range should be filtered by the = Value pair in the criteria range.
    One advantage of “advanced” filters is that it allows calculated filter criteria.
    As long as you do not modify the criteria, you can keep on working with the database range. You can add record by row isertion. You can remove records by row deletion, You can edit records. menu:Data>Refresh will refresh the database range with all its currently set sorting and filtering criteria.
    After you modified the criteria range: menu:Data>Filter>Advanced Filter (or any shortcut to this command) pops up the criteria dialog with the previously set criteria range. Just click OK or hit Enter. and the new criteria will be applied to the selected database range.
    Instead of selecting a whole database range, you can also select any single cell within that range. The selection will expand accordingly.
#20

@Villeroy Filters don’t work in the project file for a vast number of reasons. I asked a very clear, specific, question for a VBA code to be made into basic so it worked with calc. That’s what I wanted. I asked for what I wanted and needed.

#33

If the built-in features do not work for your specific project for multiple unspecific reasons, then you definitively use the wrong software. Stop wasting your time (and ours) “playing around” (your term) with spreadsheets and StarBasic code copied from other people.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.