hiding columns based on certain criteria

[Solaris]

Hello!
I have a problem that I'm trying to solve. I have a sheet full of data, with headers on row 5 and some cells here and there left empty. All headers are filled in, but there are some prices that have not been agreed upon which are then left empty. I'm using a filter on the headers, and with some filtering conditions whole columns can be left empty. What I want to do then is write a script that hides all empty columns (similarly to a normal, manual filter). However, since the column will have a value in the header row and a number of hidden values due to the filter the statement

Please Login or Register  to view this content.

will not work. My question then is, is it possible to add exceptions for the header and the hidden data to this statement (and to If statements in general)? Otherwise, can you see any other way of accomplishing this without having to scroll through all of the data, because that would be rather time-consuming?

Regards,
Samuel

[JapanDave]

How about this, change the range you want to use.

Please Login or Register  to view this content.

[Solaris]

Hi Dave,
I've tried your script with some small modifications, but it doesn't seem to do anything. Here's the code:

Please Login or Register  to view this content.

I'm guessing that it doesn't make exceptions for hidden values when it scans a column, reading up on the Find Method right now.

[JapanDave]

Those small modifications unfortunately make the code not work. It is not the find part that is the problem.

This line won't hide columns, so change this.

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

[Solaris]

Well, I actually tried running it as written before I started changing anything. :p

And still doesn't do anything :O

[JapanDave]

Best post a workbook. The code is sound.

[HaHoBe]

Hi, Solaris,

maybe try it like this (adapt the row number for the headers of the table to the last seen number of rows for it, here the header is only in the first row):

Please Login or Register  to view this content.

@Dave:
from what I understood you would have to get the header row out of the area to check.

Ciao,
Holger

[JapanDave]

My bad. Didn't even think about headers.

[JapanDave]

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[Solaris]

Perfect, it works like a charm now! Thank you all for helping me out =)

[JapanDave]

Please Login or Register  to view this content.

Bada$$!

Now why didn't I think of that.

[snb]

@JapanDave

Please start your own thread if you have a question

[JapanDave]

LOL. You will have to help me with the title as I don't know how to word it correctly for it to potentially show up in a Google search!

[snb]

@JapanDave

Probably: which brain surgeon can implant the VBA library into my striate cortex ?

[Solaris]

Name it "Why didn't I think of that?".

You're welcome. Now give me reputation plz!



Edit: Or the above. That also works.

[snb]

What reputation ? which reputation ?

[JapanDave]

So snb, where did you get your implant? LOL I need a referral.

[snb]

Some obscure Japanese Zen mental health clinic...
I will send a recommendation so you will get a bargain.

[HaHoBe]

Hi, snb,

as long as there´s only one column holding the blanks: congrats. As I had some blanks in the other columns as well I got a result from the code which wasn´t what OP had asked for (maybe I shouldn´t be so lazy as not to fill out whole areas except for one column ).

Ciao,
Holger

[snb]

@Hahobe

I tested with 3 columns holding any blanks. No problem.

[Solaris]

@HaHoBe, I am in fact using your solution. My post just happened to land after snb:s.

@snb, When I try your version my whole sheet gets hidden. I have blanks mixed in in all columns, but I only want to hide columns that are all blank.

[snb]

So why didn't you post a sample workbook ?

[Solaris]

Because the data I'm working with is confidential and property of a large company, and even though I'm sure there would be no harm I will follow company regulations and not post any data. Secondly, when I was in the process of creating a dummy sheet to post here HaHoBe gave me a working solution, so I used it and went back to work instead of continuing. I posted immediately when I had the solution, but since I didn't refresh the page before posting I didn't see your post until after. Hope that clears it up.

Regards,
Samuel

[snb]

I asked for a sample workbook (see the forum fules), not your workbook.

[Solaris]

Yes, and I was working on one as per JapanDave's request when HaHoBe posted a working solution. So I scrapped the sample, since it wasn't relevant anymore.The rules don't say anything about having to post a sample workbook after a thread has been resolved, but suggest it in order to get a faster response. It was a good thing I read them though, now I know how to change the status of the thread.

If you feel offended by anything I've written I apologize, though I have a hard time seeing why you would. I will change the status of this thread to solved.

Thank you all for your input!

Regards,
Samuel