I'm not an Excel VBA Person by any means, I can get by only when necessary.

Well this time I need to get by and I'm too tired to fight with this thing.

How do you filter a sheet based on a selection in a combo box?

I've got the combo box made, it's populating appropriately.

Now I just need to filter based on a specific column for all of the data from Row F6 and down.

Just a quick sample will help and I can get anything else I need. I just don't use Excel enough to know where to start with filtering.

- Anthony

OK, I think I'm on the right track.

Here's what I have so far:



Problem is, there is nothing indicating which field to filter on and nothing is happening.

I've been googling for the last 2 hours on this and can't find a single thing on it.

I'm gonna give it another hour and then I'll stop for the night and check in here in the morning.

Thanks All.

- Anthony

Hi Anthony,I think you could be making more work for yourself than is necessary.
(Although you might be taking a different route for a reason of course..... Are you using a form aha of course you are!!

I would set up a criteria range on the form, place the value of the cbo in the appropriate cell in the criteria range and then perform the advanced filter:

(Kind pof like the attachment, but I haven't used a form, I just created some pull downs in the criteria range.
Doing this allows you to add in the Dbase functions to the right of the tables too
Jim

OK, I'm seeing what you are doing, and that's what I want, except that I can't figure out how it's working.

I'm only searching on one column, so do I still need to put a copy of all headers and a row or two below it for criteria?

- Anthony

No, you could create a criteria area that looks at 1 column 1 row (in my e.g. the criteria range would be A1:A2)

HTH
Jim

ok, and I tried that and still couldn't get it working???

I finally quit at 4am and am getting ready to restart now, I'll try again.

- Anthony

No Probs, I'll only be about for about another 20 mins then I've got a couple of meetings then I'm off home. But I'll check back in on Monday.
Good luck.
Jim

OK, I'm going to kick myself in the @** if this is working now.

Here's the problem

- I didn't use the xlOr to include "" values, so I was getting undesired results, which kept making me change the way I was approaching it.

I think I had this thing working all along, but now here is the last problem.

I'm getting a Runtime Error of 1004: AutoFilter Method of the Range Class Failed

The code is working fine, and the error is just a pain. I'm not sure if I should just mark it off as a small bump and just trap the error???

What do you think?

- Anthony

Hello Anthony,
just a suspicion...

is the "Criteria" range two-cell range? because you have to repeat the header in the criteria,
e.g. header of column A: "Name" in A1

criteria C1: "Name", C2: the current value from the combobox

also, use showalldata method of a sheet object instead of "*" wildcard.

I am not sure if I make sense, so I attached an example (combo placed in a sheet, but the code is pretty much the same as if you placed it in a form)


Martin

OK, I'm using the AutoFilter Method now, but if the AdvancedFilter is better than I'll go that way.

A couple questions though.

How do I include more than one criteria? I need the criteria of the combo box, plus all "" values.

Secondly, I don't think I can use the .ShowAllData Method because there are other filters in place that need to be retained.

Any thoughts on the runtime error I'm receiving with the AutoFilter method?

- Anthony

As for the error - difficult to say without seeing the code. May be that the Autofilter range was not set properly?

What's "" value? Empty cell? Or a cell that contains a formula that returnes ""?

If I follow correctly, you want to show all rows where the column either fulfills the criteria or the cell is empty?

M.

Exactly. The .AutoFilter Method that I have in place now is working, except for that runtime error.

Any Ideas?

- Anthony

See the attached (modified to Autofilter).

Is it closer to what you need?

OK, Yes, that's what I have already.

The only problem is the [self censored] Runtime Error

Any Ideas? Could it just be my machine? Unfortunately I can't post it here to have you check or I would.

- Anthony

Just called Client and will be having them test it now to see if it's just my machine.

If you have any ideas though I would very much appreciate it.

- Anthony

I am sorry Anthony, I have for sure seen this error before but I don't seem to be able to reproduce it...

The only case I could identify so far is when you try to reference a field number that is higher than number of columns in the range, e.g.:

Range("A1:A5").AutoFilter Field:=2, Criteria1:="B"

will trigger the error you described.

Can you at least post the piece of your code that triggers the error?

I have the following code:



Which works fine, except for the runtime error.

The Range 'NewData' is a single column and about 200 rows.

The range has a header, and the data is everything that is in the combo box as well as blanks.

- Anthony

I can't reproduce the error, see attached (Sheet3).

Is the combobox now placed in a form or in a sheet? (Not that it matters, I guess)

Invalid Zip!

Can you try again. Cause if it errors on my machine, then I know it's my machine.

- Anthony

&@&@#!!!

Try now...

OK, Now I'm confused and pi$$ed.

It's not erroring with yours, but it is with mine???

Same code. Can you think of anything to check?

- Anthony

I checked MS KB, nothing... so just shots in the dark...

In one of the posts you mentioned you have more filters in place, a sheet can IMHO contain just one Autofilter. Shouldn't barf though, the second Autofilter just doesn't work as it should.

Could be you have the same name twice in your workbook (once on a workbook level, second time on a Sheet level?) See in the Names=>Define if it's just once there.

Try to replace "" with vbNullString...

Try to prefix the explicit sheetname:
with Sheets("Sheet1").Range("NewData")...

Martin

Yeah, I just found that the AutoFilter doesn't work twice. However, the thing is that both AutoFilter are involving the same thing, just different sections, so I'm joining the range and giving that a shot, I think

Do you have any other suggestions?

Client dropped this WorkBook on me last night and needed it by this morning. I was up til 4am, and have been on it for about 4 hours already today.

One small bit of code, and it's taking this long. Just too long.

Any other suggestions or methods?

- Anthony

Does the code always barf or just for show-all or show-one option?

what if you remove the previous autofilter from the sheet and then run the code?

How do you define the range? If A1:A200 is your range and A1 contains header, Name "NewData" is defined as...?

Also try

With Range("NewData")(1)

The Range is F5:F255

F5 Contains the Header.

The code barfs on all options.

I tried using: With Range("NewData")(1) but it's still barfing.

I removed the other AutoFilter already, and the error still exists.

Any Ideas?

- Anthony

Yes, let's wait until Nate or Ivan F Moala appear...

No, seriously, I'll try to search a bit for some other hint. It's a pity you can't fill the sheet with some dummy data and post it... (can you?)

:(
martin

Unfortunately No. All of the formulas are client sensitive and seeing as it works with your example which has the same code, I'm thinking that it has something to do with the format of either the combo box, or the data I'm filtering.

A Couple things, just in case they might shed some light.

The data for the combo box comes from a range.

The data that I'm filtering is either blank, or filled via a cell drop down list.

The format of the data that needs to be filtered was Number, I changed it to General to see if it would fix it, but that didn't either.

- Anthony

One more thing.

Do you see any issue with just trapping the error, if we can't find the cause?

I trapped it and just resumed next and it was fine, but I'm wondering if I might be setting myself up for something by doing that.

- Anthony

OK, Just got off the phone with the Client and he wants me to just trap it and move on.

The filtering is working fine, it's just that the error pops up after the fact.

We don't see any way of this coming back to bite us, so we'll just trap it.

however, if someone can please shed some light on this, I would very much appreciate it.

- Anthony

I have developed a macro which simplifies using the Advanced Filter command. I have attached it to this post. Help yourself.

Hmmm...

I got a reference error, cleared Word 10 reference (I use Office 2k) and now I get 'no database' message.

Never mind - I have cooked up several customized Advanced filters already, but thank you for your post

Regards,

Martin

P.S. Incidentally, I see quite a heavy use of selections in your code...

Hi Anthony, Good thing Jerry doesn't monitor this forum, he'd probably tell you your sheet is not normalized.

Seems funny seeing you struggle. How do you like being on the DumbA$$ side?? LOL! For what its worth I can spell E.X.C.E.L. and thats it.

Hi Dave,

You don't expect us to believe this, do you?

I admit, Anthony's problem drives me crazy... I am sure I have encountered such a kind of error before and the solution was quite trivial. But now, when I need it, I can't and can't reproduce the error. How typical.

Cheers,

Martin

Actually Martin its true. I know very little about Excel. I know its a high power app, but always believed for the low level of number crunching I do, I could do in Access. But I do not mean to demean its necessity or viability as I know it can do amazing stuff with it.

Didn't mean it that way...

Honest for honest - wish I were able to do with Access what you guys can do...

But yes, I love Excel

Cheers,
Martin

Martin, I didn't think you meant it any particular way. LOL, This is the only time I have EVER posted in an Excel thread, (and I am certainly not bring anything to the table here) normally when I open thread and see its an Excel question I run like Hel#. But I know you guys will get my bud Anthony through it. o!