Help to fix macro: hidding multiple rows

Please Login or Register  to view this content.

jindon your line works, but it also stop working the previous line:

Please Login or Register  to view this content.

How can I have both lines working? Thanks

Please Login or Register  to view this content.

but Why 2 lines?

Please Login or Register  to view this content.

Sorry my line had a mistake. The first line was supposed to be k15 = CORRECT, the second line k15 = INCORRECT

AliGW, please refer to the previous post. Correct code is:

Please Login or Register  to view this content.

Should work

Please Login or Register  to view this content.

Very strange jindon, the first line is working the opposite of the second. This is my real macro, exactly what I have in my spreadsheet:

Please Login or Register  to view this content.

The second line is working well, but the first line is hiding rows 21 to 27 and keeping all the other rows not hidden.

Any idea?

As I said, why 2 lines?

21:396
1:20,28:396

means 1:396

See post #15

Sheets("Car Mileage").Rows("21:396").Hidden = [K15].Value = "METHODMONTHLY" - when I select the MONTHLY method in the cell K15, I will see only lines 1 to 20 which have the monthly structure, and all the other stuff between 21 to 396 should be hidden
Sheets("Car Mileage").Range("a1:a20,a28:a396").EntireRow.Hidden = [K15].Value = "METHODYEARLY" - when I select the YEARLY method in the cell K15, I will see only lines 21 to 27 which have the yearly structure, and all the other stuff between 1 to 20, and 28 to 396 should be hidden

Sorry for confusing you guys.

Then you don't seem to understand the logic.
when [K15].Value = "METHODMONTHLY"
hide
Rows("21:396")

UNHIDE
Range("a1:a20,a28:a396").EntireRow

and other way around when [K15].Value = "METHODYEARLY"

That was not for you.

I made it clear, my post is just under the post form the OP.

Can you please stop doing like this???

jindon I made the attached sample spreadsheet, you can see there is something not working properly. Could you please have a look please?

As you said in your post before, I might not understand the logic and I am probably doing something wrong.

Thanks for your help mate.

Cheers,

It works well jindon, but what if I have many rows to be hidden with many different conditions? The code below I created before noticing the problem and therefore before creating this thread:

Please Login or Register  to view this content.

Sorry I thought the solution was going to be simple so that's why I just posted that two lines in my initial post.

How can I adjust your macro from your latest post with so many conditions?

Cheers,

OK, you make it hard for yourself, so let's think it other way around.

Which row(s) do you want to UN hide with the condition(s)?

Yes, the macro works fine if there is just only range. The problems is 6 code lines have more than one range.

Which rows I want to unhide? The opposite rows that need to be hidden
For example, first line from my previous code, unhide 1:20. Second line, unhide 21 to 27. Third line, unhide from 28 to 36, and 38 onward. And so on...
If you have the code, just send me for the first 3 lines and I can complete the rest.

Cheers

Sucuri doesn't accept to post my code, so in the text file attached.

You are truly a legend and I am truly an annoying person: I forgot to mention that both "Car mileage" and "Income" tabs are password protect.
Where should I insert these lines?

Please Login or Register  to view this content.

I think after that it would be all cover!

Cheers,

Just one below and one above With and End With

Please Login or Register  to view this content.

And same for "Income" sheet.

Sorry to say but

- Car Mileage bit of the macro doesn't seem to be working at all.
- The Income part seems to be working besides the case K18.
- I am also having trouble with this line: If Not IsNull(myVal) Then .Rows(myVal).Hidden = True. Is this line assuming a blank cell? The cell might be blank but not necessarily. Can this line be adjusted to "if the cells are different than NO, then show rows"?

Cheers

It should not hide the row(s) when the value <> "NO".

Anyway, I just coded based on your code, so I have no idea that it does.

Whoops..,

Please Login or Register  to view this content.

Hi jindon,

Unfortunately the code is not working, but I have made the attached sample spreadsheet and now you can see your code in action.

I amended the code as per your previous post, but only cases E16 and E17 are working, all others are not.
Also if you have all cases selected as "NO" in the Income tab, none of the rows should be showing, but this is not happening. Apparently the macro just works if selected one NO, if I select two NOs, just one of the NOs will hide the rows.

In the sample spreadsheet, I colored the rows with different colors so you can better identify what should be hidden or not when selecting the options:
- in the "Car mileage" tab, it should show only one color. A row might show more than a color, but that's fine as long as the main color is there.
- in the "Income" tab, it should show only one color PER METHOD. A row might show more than a color, but that's fine as long as the main color is there.

Please let me know if you can figure out what is happening with the code.

Thanks jindon

Hi jindon, would be great if you could help with the above.
Cheers

I'm out at the moment.
I wll get back to you later.

Just looked at your file...

You have partially colored rows in " LOGBOOK METHODPARTIAL" sheet.
Can you show me the result you want when each one of the method selected?

The layouts you sent is not feasible to me.

Hi jindon, sorry I didn't receive a notification email when you replied on the 17th.

Please find attached what would be the result if selected "LOGBOOK METHODPARTIAL" (only red rows would appear).

So the idea with the colors is, when something is selected, only that color would appear (although sometimes multiple colors would appear, which is fine as long as the selected color is still there).

Cheers,

Can you do it manually for yourself? Hide all other range except red colored range?

I don't think it is possible...

Other people will use the spreadsheet, this code is essential for my project :/

Isn't it possible to code: if K15 equals to "LOGBOOK METHODPARTIAL", hide columns 1 to 28, and 33 to 37 from Car Mileage tab?

And so on for all other options.

What I'm saying is that you can not partially hide columns/rows, means you can only hide whole column(s)/Row(s).

You know what I mean?

Ah yes that's definitely fine, all good to hide the whole row.

The colors in the first three columns in the sample spreadsheet are just for demonstration purpose only.

Then I need to see exact range(s) that you want to show when each one selected.

jindon I am working on the conditions now, but just wondering if instead of which rows want to show, can it be which rows want to be hidden?
Show conditions are much more complicated than hide conditions.

If it is logically understandable and covers everything, OK.

However your conditions seems multiple criteria now, so Change event is not the way to use.

And I need to go out now, so my next response will be late.

I'm trying to simplify the conditions since your post one hour ago.
Do you have any suggestions?

Just coded as per your instructions.

1) Delete existing code in "Settings" sheet code module.

2) To CAR MILEAGE sheet code module

Please Login or Register  to view this content.

3) To Income sheet code module

Please Login or Register  to view this content.

jindon the code is almost perfect, almost there, but just a couple of problems in the second code:
1) in the CONDITIONS spreadsheet I sent before, please see in rows 12 and 17 that there is a condition: if "NONE OF THE ABOVE", hide the respectively rows. I think this part missed in the code.
2) cases 18 and 19 are not working properly. Case 18 works well when different than 3-YES, but still hides a few rows (225:238) when equal to 3-YES. Case 19 hide well rows when equals to 4-YES, but it doesn't UNHIDE when it is different than "4-YES".

Sample file attached demonstrating the problems.

Is there a way to fix these issues please?

Very much appreciated! Cheers

Im not with my pc, so I will reply tomorrow.

ricdamiani

Can you just include everything in one file, I mean conditions?
And also all the drop downs should show the list, so that I may be albe to understand.

I don't normally look back the file and the code as it is now using different method.

Hi jindon, please find amended spreadsheet attached as per instructions. Cheers

Try change to

Please Login or Register  to view this content.

The macro is now working perfectly jindon, but what if the Income sheet is protected?

I tired to insert .Unprotect "PASSWORD" below the "Application.ScreenUpdating = False" line, but it didn't work.

Cheers,

You need to use "Me" key word.

Please Login or Register  to view this content.

Awesome, thank you very much jindon

You are welcome and thanks for the rep.

jindon, I changed a bit your code for a new tab as per below. It is working properly, but the only problem is if e12 is not CENTS PER KILOMETRE METHOD, it should do nothing. How can I add this logic to the code?

Logic: if e23 is not CENTS PER KILOMETRE METHOD, do nothing

Please Login or Register  to view this content.

Cheers

jindon, are you there?
Cheers,

I'm in the middle of long holidays, so hard to response now.

If only one condition

Please Login or Register  to view this content.

Of course, sorry I forgot about that.

Thanks and happy Golden Week!

Cheers

No problem, easy to forget when you think it over and over.