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

English
#1

I found this VBA code that does exactly what I want it to and is written (editable) exactly how I need it:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range(“C5”), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = “Canada”: Rows(“19:25”).EntireRow.Hidden = True
Rows(“7:18”).EntireRow.Hidden = False
Case Is = “India”: Rows(“19:25”).EntireRow.Hidden = False
Rows(“7:18”).EntireRow.Hidden = True
End Select
End If
End Sub

How would that script be written for Calc Basic?
I don’t need it to be private.

#8

Basic is a language of the past. You can hardly do anything with it except writing macros for office suites or write other programs exclusively for Windows. With Python you can program anything you want for most of the existing computing platforms. Basic is a lot more difficult to learn than Python is.
Doing the first programming steps in Basic in the context of this extremely big, fat, complex office suite, is like training for the first pilot licence on a 747 using a garden chair as pilot seat.

On topic: The built-in “advanced fIltering” feature filters spreadsheet lists by cell values. I recommend to abstain from list keeping in sheets. Even the most simple list should be kept in a database when it comes to sorting, filtering and looking up items in related lists.
No matter how many lines of silly macro code you throw at your spreadsheet, it will never come close to the functionality of a most simple database.

2 Likes
#11

I’m old and don’t have the brain power, patience, or time to learn a coding language anymore. I just want to get this sheet working the way I’d like it working. Filters won’t work. I played around with them for many hours before asking for help writing a macro to do it.

#14

@Bort, take your time and consider my decision. Following the advice from @Villeroy, I use an advanced filter, but a somewhat simplified implementation.
The advanced filter exceeds all imaginable and unimaginable expectations. But we need to invest a little time in studying it. A minimum of lines of code. Following the KISS principle…
hideRowsByAdvancedFilter.ods (18.8 KB)

Regular expressions, filtering values in a field in a given range (between), using formulas in criteria, and much more are left for later…

1 Like
#15

@eeigor: good Solution!
may be the OP can do it this way.

#18

This does not work for what I want to do. Please read the question. All I wanted was my question answered as I asked it. There is a plethora of reasons the project document can’t be changed and I wanted it the way I wanted it. One of those reasons is the project file is over 3 years of a work in progress. Please keep in mind that when questions are asked all people want is that question answered.

#19

:joy: Oh LOL

If only you ever spent some time answering people, you would learn that your happy case is rather a corner case, and in 90% cases when people ask something, what they really want is something completely different. E.g., XY problem. Or terminology problem.

1 Like
#21

I gave technical support for php-nuke packages to millions of people for nearly 20 years. I was part of the development team that originally created them. I’m fully aware of what it’s like answering questions and giving technical support and writing custom code and even applications for people that don’t know how to write that kind of code (for free).

This is disgusting to me that when I ask a question here the answers I get either aren’t an answer at all or if they are an answer but they completely change and do an almost 180° opposite of what I asked for. This question in particular was a clear, direct, specific question and look at all of the irrelevant nonsense, discussion that’s usually not allowed or appropriate on Q&A sites, and opinionated rants insulting the suite (calling it stupid and I didn’t read most of villeroy’s other unwanted, inappropriate, comments) I got that I didn’t ask for.
All I wanted was the code converted. And you’re trying to convince me, who probably has way more experience than any of you with giving free technical support for software they wrote, how to act?
Who’s wrong? YOU ARE.

#22

@KamilLanda has hard-coded the task. Very bad, because it is “hard-coded”. Let’s thank him for the fact that he still wanted to do it.
@Bort, we can all do it, as he did, but I would not do it, because we are not only answering questions, but also engaged in self-development, and we want to move forward on the right path, not sideways, not backwards. And we don’t want to increase the level of entropy in the universe. :slightly_smiling_face:
I reasonably assumed that if the row numbers are known, and the associative values are also known (in this case, the names of countries), then let machines work with the numbers, and it is easier for people to work with meaningful information.
Procedural, hard-coded solutions are absolutely inflexible, difficult to maintain, and so on. This should be avoided if possible. I would like it to be less. After all, we work in LibreOffice Calc, and a lot has already been done before and for us.
@Bort, we don’t need “pants inside out” (as you once put it). Just don’t scold me here, please.

Alas, I still “insist” (I’m joking, of course) on changing the filtering criteria from row numbers to meaningful values. We are people. And, without seeing a specific example, it is difficult for me to say what needs to be changed in your example. Obviously, I shouldn’t have said that…

On the other hand, my example is focused on the work of the user, not the program. Maybe I guessed wrong. Should I have to guess? But someone makes changes: Private Sub Worksheet_Change
Please formulate the task in such a way that it is also interesting to us (and not “exactly” and in no other way).

@karolus, thank you for appreciating the example.

#23

You’re free to have your opinion of course. I don’t want your opinion and especially don’t want anyone’s ridiculous rants in my questions as a discussion on a Q&A site. I’m not your mom and this site isn’t your shrink. Get some help, call a hotline.

#25

Ignored forever. Toxic. :radioactive: Farewell.

#26

I enjoy this case of someone having absolutely odd ideas about who they are talking to. Being on a user-to-user Q&A site, with everyone here most likely being just another (possibly experienced) fellow user, who may help, or may have other ideas that usually meets warm thanks from askers, here we have someone who comes with requirements to those who may dare to answer. Hey you miserable regulars here! Don’t even think to suggest your solutions, until you read and comprehend all the list of requirements that this asker has imposed to us. I like this attitude - let’s put it as #1 to our “how to use this site”!

#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!!

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