VBA Excel - Multiple Combobox - Set Blank = "All" (epoiezam)

[epoiezam]

Hi again guys:

In a bit of a jammed here,
Again this is the best site I turn to, for help.
I paste down my script below for your easy viewing. (it's a sample, overall I have 10 comboboxes)

I don't have any prob when all of the comboboxes are selected (not equal to blank). it works beautifully.
But when I leaved some blank. Then the filter doesn't worked.

Thank you in advance for your kind help.


last = Cells(Rows.Count, 4).End(xlUp).Row
For i = last To 1 Step -1
If Cells(i, 4).Value = combo1 And Cells(i, 5).Value = Combo2 And Cells(i, 6).Value = combo3 Then
Cells(i, 4).Select
Range(Cells(i, 3), Cells(i, 24)).Select
Selection.Copy
nextrow = Range("z5000").End(xlUp).Row + 1
Cells(nextrow, 26).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If
Next i

[Tinbendr]

Try changing your And's to Or's.

[epoiezam]

Hi Tinbendr:

First.. Thank you so much for responding.
In this scenario, I need "blank" to equal to "All"
Is that possible ?

Sample of required outcome in Table A below:-

If:
Combo1 = Matured Adult (A3)
Combo2 = "Blank" (A4)
Combo3 = MASS (A5)

When Combo2 "blank", it will display all values (column A4)
Same goes with the rest of the combos. If no value selected ("blank").
It will read as "All".


Table A

A3 A4 A5
1 Matured Adult A MASS
2 Matured Adult A MASS
3 Matured Adult B MASS
4 Matured Adult B MASS
5 Matured Adult C MASS
6 Matured Adult C MASS



Thanks again Tinbendr

[mikerickson]

Instead of

Please Login or Register  to view this content.

you could do

Please Login or Register  to view this content.

This might cause problems if possible values for combo1 etc have ?, *, or # as characters.

[mikerickson]

A more robust test, that won't be effected by special characters would be

Please Login or Register  to view this content.

[epoiezam]

Woooo mikerickson.. that works perfectly...

Thank you, Thank you so much..

Tinbendr, Thank you.

You guys are the best..