autofilter sheets based on cell value and hide sheet if value not present

[desibabuji]

hi guys,

I have a workbook that has a total of 10 sheets. Where the first sheet has the cell A1 as the project name and the sheets 2 to 10 have miscellaneous data related to projects, and column B has the names of the projects.

I want to autofilter sheets 2 through 10 on column "B", based on the value of Sheet1'CellA1'. If any autofilter fails (because the value is not present in that sheet), I want to hide (or delete) that sheet because it does not belong to that project.

Can you please help me with this? Thanks in advance for your time.

[HaHoBe]

Hi, desibabuji,

as long as there is data in all of the sheets (you would need to alter the name Overview to suit the sheet name to hold the data to be compared), this code will delete the sheet(s):

Please Login or Register  to view this content.

Ciao,
Holger

[desibabuji]

Hi Hahobe,

Thanks for the macro, the first part to filter the data works perfectly. But the macro to check for returned values or not doesnt work :-(. I replaced the .delete with a msgbox and it gives me the same values all the time, whether the autofilter retuns some values or does not return some values.

Is there another way to check for the autofilter NOT returning any values? maybe something like this (even this doesnt work, maybe I am missing something here)?

Please Login or Register  to view this content.

Let me try to get a sample worksheet and upload it. Thanks for your help.

[HaHoBe]

Hi, desibabuji,

But the macro to check for returned values or not doesnt work :-(. I replaced the .delete with a msgbox and it gives me the same values all the time, whether the autofilter retuns some values or does not return some values.

You set the sheet to be Sheet1 in any case while I referred to ws in the loop.

Ciao,
Holger

[desibabuji]

I did change the variables. This is the code that I am working with to test it. I get the "data found" alert even when the autfilter doesn't return anything.

I have commented out some of it for testing.

Please Login or Register  to view this content.

[HaHoBe]

Hi, desibabuji,

you work with only one sheet so itīs not surprising to always get the same result for the very same sheet.

Ciao,
Holger

[desibabuji]

Hi Holger.

I am working with only one sheet to test the data but I change the A1 on overview to make sure that the filtered data changes from nothing to different values !! I commented the code to figure out what's going on.

A sample sheet has about 3000 lines and when I run this

Please Login or Register  to view this content.

to find out if there are any NON blank cells, it still gives me a value. Is there a better way to check if the filtered row area actually blank and dont have any data?

You know how when we filter manually , the bottom left of the excel sheet displays "5 of 300 records found", when it find 5 records out of the 300. Is there a way to capture that number (5 in this case)?

Do you know of any place where I can read up on autofilter? I was trying to read MSDN docs but that doesnt help !

[HaHoBe]

Hi, desibabuji,

it seems you know better how to comment working commands out than me to write them . Change the Column to suit:

Please Login or Register  to view this content.

Ciao,
Holger

[desibabuji]

I dont know a lot, I am just reading around and trying to figure out Thanks for your help. It really helped me to figure out the solution !