Need help creating macro to hide rows based upon whether 1 of 3 rows have data

In searching and trying stuff on my own I copied and edited the following code, which work on checking Column G. That's great. I just need to figure out how to have it search columns H and I also and if there are cells with data then will not hide them.

Please Login or Register  to view this content.

Any thoughts?

Maybe:

Please Login or Register  to view this content.

thank you John for your response. I tried using the code you gave and edited it a little, but doesn't seem to filter on the columns like I was hoping. It also locks up spreadsheet so I cannot undo any of the changes it makes.

Any advice on how to use this code or what it is doing?

Thanks again!

Ed

Originally Posted by BigEdJr

thank you John for your response. I tried using the code you gave and edited it a little, but doesn't seem to filter on the columns like I was hoping. It also locks up spreadsheet so I cannot undo any of the changes it makes.

Any advice on how to use this code or what it is doing?

Thanks again!

Ed

The code doesn't make any changes perse. It adds an x in the next column with data and filters on that. Just turn your autofilter off.

Here is the code after my changes:

Please Login or Register  to view this content.

All I did was try to make it work starting at the filed I wanted, G7. But it is not filtering rows that have nothing in the G, H, or I columns. In fact it looks like it is filtering rows that have something in all of those fields.

I would like to try and filter or hide rows based on whether there is something in column G, H or I. For example setting a range of G7 through I7, if it is blank hide it, if it has a value in one or more of those cells leave it.

I will set up a query for each date on my spreadsheet, so the next set would be J7 through L7 and each of these sub procedures would run after a button was clicked.

I am going to create a procedure that unhides or clears the filter before moving on to the next button etc.

Thanks guys!

Ed

Try:

Please Login or Register  to view this content.

BTW: I like the way my first code works, but I only get it to search on column G. I am not sure how to make it search through G, H and I. If I could get that done it would be perfect for my needs.

Ed

It is doing the same thing. I attached a couple of screen shot below.

Not sure what is going on.

Before:
Before.jpg

After:
After.jpg

I can't duplicate your results? One the sample you provided I Get - Name1, Name2, Name3, Name8, Name9, Name11, Name12 & Name13 showing. From my understanding that's what it should be. Here is my last offering.

Please Login or Register  to view this content.

Thank you John. I am sorry about the confusion I caused with my separate spreadsheet. I had tried to hide some confidential data in my original spreadsheet and inadvertently deleted a couple of rows and did not send the whole thing etc. So thank you for putting up with me and your continued assistance.

What it looks like is happening with your code John, is that it is filtering ALL rows by data that is in ALL the columns. That is great I think I am half way there with that. So Thank you very much for that.

What I am hoping to figure out is that I want to filter on just a specific set of columns, like G:I under the date 3/1/2014 and then clear the filter and filter on the columns under 3/2/2014 which would be J:L etc.

I am trying research how everything works and not just ask for a finished solution. To that end can someone explain how the UsedRange property works? I am looking everywhere but cannot find a good description. Can it be defined as to which columns are designated as part of the filter etc? So how do I set it up to filter on just G through I? Then I will create other subs for the different needs.

Thank you again for everyone who has taken their time to help. I really do appreciate it.

Ed

Ed:

Sorry I was the one who was confused.

To remove Filters.

Please Login or Register  to view this content.

This uses a Helper cell BC1 to advance the filters. In BC1 or another cell of your preference type in 7.

Please Login or Register  to view this content.

Probably better methods though.

Thank you again Jason. It just does not seem to be doing what I need and it was adding some weir stuff at the end of the columns. But thank you for your time and effort.

I would like to try using this code in some way:

Please Login or Register  to view this content.

As the comments say it partially is doing what I like, but I cannot figure out the logic to hide only the rows that have no data in any of the 3 columns. At this time it is doing an all or nothing kind of thing.

How can I use and OR type of statement to tell it to treat each cell individually?

I also attached the spreadsheet I am working with now Hide Rows Testing B.xlsm

Thanks,
Ed