How to Hide/Unhide rows based on condition

[Kfir7]

Hello experts,

I have a table in range B11:J1010
Column E11:E1010 indicate date in each row.
Cells E1 and F1 are a dropdown and set by the user.

I need a help doing the following:
If E1="Annual" then show all the rows in range B11:J1010.
If E1="Monthly" then show ONLY the rows that contain the month as appear in Cell F1 (F1 indicate month and set by the user).

The check, whether a row fit or not fit to the selected month, can be applied by using MONTH function:
MONTH($F$1)-MONTH(E11)
MONTH($F$1)-MONTH(E12)
...
MONTH($F$1)-MONTH(E1010)

The condition:
If the result is zero show this row.
If the result is any number other than zero hide the row.

When the user set F1 to another month the rows display in the table need to be updated accordingly of course.
When the user set E1 to Annual all rows should be re-displayed and so on.

I looked over some threads looking for an answer but unfortunately none of them gave me the answer.
Any help to implement this will be greatly appreciated.

Thanks in advance
Kfir

[arlu1201]

Do you have a sample file that you can upload? It will help us test the solution instead of creating one from scratch.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Kfir7]

Hi arlu,

Thank you very much for your swift reply.
I will tell you what... the file that i'm working on consist of more than 1 worksheet and it is not in English.
If my description above is not satisfactory then i wil try to make a sample and attach it here.
If you have any question or you need more info regrding the issue please let me know.

Best regards
Kfir

[Kfir7]

Hi arlu,

I have attached a sample file as per your request.
Hope it will help.

Regards
Kfir

[Kfir7]

Hi Arlette,

Any update?

[arlu1201]

Do you always have data till row 1010? How about which is the last row having data and then hiding/unhiding the rows? If you always run it with 1010 rows, its going to take a long time each time.

[Kfir7]

Hi Arlette,

Good thought man (-:
How long it's gonna take? If it whithin range of milli seconds then there is no problem.
At this stage I prefer it will run on the whole rows i.e. till row 1010 like the AutoFilter on column C does.

Thanks

[arlu1201]

When i ran it at my end, it took about 1 min i guess for each run of the macro.

[Kfir7]

Hi Arlette,

One minute it's a very long time. How come the AutoFilter does the same job whithin 1 second on 550 rows?
I assess the latency influenced also by the strength of the machine that runs the program.
I suggest i will run the macro on my machine and see how lont time it takes. If it takes 1 minute also on my machine then we should adopt you suggestion.
BTW If we will do it till the last row that has data like you suggested before do the empty rows will be displayed in the result? How long time it will take that way?

Thanks a lot
Kfir

[Kfir7]

Hi Arlette,

How are you doing?
Do you have the macro ready?
If so, can you please attach it so I can do a test?

Best regards
Kfir

[Kfir7]

BTW Arlette, if you think there is a better or faster way do the check along the rows without using MONTH function feel free to do it.
Keep me posted please.

Kfir

[arlu1201]

Sorry, was busy. Will work something out for you by today.

[Hercules1946]

Hello Arlette/Kfir
I have an older version of Excel and Im not sure if its converting correctly, but I was thinking....
If speed is an issue, could you have a second sheet in the workbook for the monthly version that
already had the rows hidden that you dont want to see. Then you could add a macro that runs
when the workbook opens asking which display you want.

HTH

[Kfir7]

Sorry, was busy. Will work something out for you by today.

Hi Arlette,

Have you made up something for me?

[arlu1201]

Right click on your sheet and select View code. Then enter this code in -

Please Login or Register  to view this content.

[Kfir7]

Hi Arlette,

Thanks for the code.
I have added the code to my worksheet through the View Code option but unfortunately nothing happened.
Have you verified this code? If so, do i have to do anything else except for paste the code in order to load it?

Regards
Kfir

[arlu1201]

You just have to copy the code as per the instructions i gave you and then select the dropdowns.

[Kfir7]

Well, this is exactly what i did but nothing happened )-:

[tigeravatar]

Kfir7,

Attached is a modified version of your posted workbook.
In the sheet 'Financial Report' sheetcode is the below worksheet_change event. You can view it by pressing Alt+F11 and double-clicking Sheet1 (FinancialReport).

Please Login or Register  to view this content.

[tigeravatar]

Remember that you will need to Enable Macros in order for the worksheet_change event to trigger.

[Kfir7]

Hi tigeravatar,

Thank you very much for your help.
Your code indeed do the job but there are some bugs

• E1=Annual and F1=2012
  When entry is added or modified in one of the columns on any row the whole table disappears except for the lines of July (critical bug).
• E1=Monthly and F1=month
  When December is chosen it shows also the empty lines.

By the way, is there any way do the following:
If E1=Annual then show the year in F1, otherwise use Monthly (already exist) as drop down list in F1.
If so it will be very helpful since it will avoid the intermediate states which in fact are illegal (e.g. E1=Annual and F1=month or E1-Monthly and F1=year)

Regards
Kfir

[tigeravatar]

Updated code, this should take care of those bugs:

Please Login or Register  to view this content.

[Kfir7]

Hi tigeravatar,

Thank you very much for your swift reply. Those bugs indeed were fixed
Can you please add the following to the code: If E1=Monthly;F1=month and the user change E1 to Annual nothing will happen until F1 will also be changed to year.

Unfortunately i have found another critical issue. The "Displayed Customers Only" feature not works with the Monthly filter. When using the AutoFilter it works fine like before but when using the Monthly filter nothing happen and it always shows the details for the all customers.

Your help is greatly appreciated.

Regards
Kfir

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[Kfir7]

Hi tigeravatar,

Your code works like a charm!

I have one critical question about your code.
What will happen next year? In other words the question is:
Does the code know how to deal with year which is not equal to 2012?

Best regards
Kfir

[Kfir7]

Hi tigeravatar,

Well, the joy was a little bit premature I encountered a new critical issue that associated with your latest update.
When using the drop-down lists in F1/E1 for the first time, it abrogates the AutoFilter in C10.

Regards
Kfir

[Kfir7]

tigeravatar, are you still around?

[tigeravatar]

Kfir7,

It should already be able to handle years that are not 2012
This is the first I've heard of a filter in column C. What are you filtering on column C for? Or do you just need the filter button on column C to be available?

[Kfir7]

Hi tigeravatar,

Well, the AutoFilter was there from the beginning
The AutoFilter on column C not filtering the column but the whole table.
It allows for example to filter the table by customer name or filter the table by color etc.
In user perspective this filter is very significant and important.

Regards
Kfir

[tigeravatar]

Try changing this:

Please Login or Register  to view this content.

To be this instead:

Please Login or Register  to view this content.

[Kfir7]

Hi tigeravatar,

Thank you for your reply.
Here are some bugs i have found with the latest code:
1. If E1=Annual and F1=year and the user change E1 to Monthly the table disappears. Normally in this case nothing should happen until F1 also changed to month.
2. When filtering the table by month using the new filter (E1=Monthly;F1=month), it adds filters to the all header columns (column B to column L).
3. The new filter ignore the AutoFilter in C10.
For example:
Use the AutoFilter in C10 and filter the table by customer called Systematics.
Now use the new filter in order to show only the rows of March (E1=Monthly and F1=March-13).
Bug: The new filter refers to the whole table and shows all rows of March instead of the rows of March of Systematics only.
4. When changing back E1 to Annual and F1 to year it erases the AutoFilter in C10 and shows the whole table instead only the rows of Systematics.

P.S.
If you want to restore the AutoFilter in C10 select cells C10:C1010, go to Data tab and click on Filter within Sort&Filter branch.

Best regards
Kfir

[Kfir7]

Update:
Bug #1 in my previous post was resolved by updating the code.
I have updated manually the year in the following line to 2013:
If Len(Me.Range("F1").Value2) = 0 Or Me.Range("F1").Value2 = 2013 Then GoTo ExitEvent Else GoTo HideRows

Is it possible tell the code to take the year from a specific cell in DB sheet? If so this could be an easy solution.
In this case the user would have to enter the correct value once a year in DB sheet without dealing with the code.
Attached is an updated file with the fix of bug #1 and with a small change i have made in DB sheet so the code can now take the year from cell E5 in DB sheet.

Regards
Kfir

[tigeravatar]

Kfir7,

The point of this forum is to learn how to do things yourself, not have others work for you for free. At this point, if there are bugs in the code, you need to learn how to correct them yourself, or hire an Excel consultant to do it for you.

[Kfir7]

Hi tigeravatar,

Thank you for your fast reply.
Unfortunately i don't have much time these days to learn VBA from the beginning (i wish i could )
For the avoidance of doubt, this workbook is a favor i do to a family member and it absolutely not for profit (!)
From my point of view, forum intended to help people and to share information and knowledge and if you feel you work for me then i can only be sorry.
This thread is public and i can promise you that your help will be helpful not only for me as you tend to think, but also for the other people see this thread.
Anyway, thank you for your previous help.

Best regards
Kfir