PDA

View Full Version : [SOLVED] VBA FILTER



olegvolf
08-20-2017, 03:31 AM
Hello

I need some help with the user form
in the attached file
the userform filters only 5 first ones


How can i filter 5 different columns that in not necessarily one after ather
the sheet "material" is my working sheet


Thank you

p45cal
08-20-2017, 07:28 AM
Lots to do, lots to do.
First, to answer your question, or rather part of it, you have a snippet of code:

For col = 1 To 5
col2 = Choose(col, 248, 250, 252, 254, 256)
.Range(.Cells(1, col), .Cells(LastRw, col)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, col2), Unique:=True
Next col

You can do something like:
For col = 1 To 5
col1 = Choose(col, 2, 5, 12, 14, 17) '<<these are the columns you want to include.
col2 = Choose(col, 248, 250, 252, 254, 256)
.Range(.Cells(1, col1), .Cells(LastRw, col1)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, col2), Unique:=True
Next col


Other things:
You have:
Set rSource = .Range(.Cells(2, 1), .Cells(LastRw, 5))
which clearly you can't use anymore.
Later on you have:
Set rSource = .Cells(2, 200).CurrentRegion
which you use later to reset the .rowsource of the listbox.
It's a completely different area.

Instead of using different areas at different times as a source for the listbox, I'd use the same(ish) area from the outset. If that area were on a completely separate (hidden) sheet that would be even better; it would likely help the following scenario with your userform:
If you fire it up, select HIGHC in the first combo and hit the Filter button, then select LOCK PIPE in the third combo, and hit the Filter button, you still have a big list in the listbox when there should be just one row visible. I haven't looked too closely but I think this is because
.Cells(1, 200).CurrentRegion.ClearContents is not fully erasing data because some rows are not visible - you'll have to check.

I'd put the comboxes' .rowsources on the same separate sheet too.

I'd update the comboboxes' .rowsources after every press of the Filter button too, so that (like doing an autofilter) the lists to choose from get shorter and shorter.

So instead of
Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)you could have
Set rSource = Intersect(Range.AutoFilter.Range.SpecialCells(xlCellTypeVisible), myColumns)where myColumns is something you've set up earlier with the likes of
Dim myColumns As Range
ColmArray = Array(2, 5, 12, 14, 17)
For Each c In ColmArray
If myColumns Is Nothing Then Set myColumns = Sheets("MATERIAL").Columns(c) Else Set myColumns = Union(myColumns, Sheets("MATERIAL").Columns(c))
Next c
'Application.Goto myColumns' just to show where the range is.


Likewise, instead of deleting .current region, delete entire columns - like you have already done in
Range("IM:IV").EntireColumn.ClearContents

There seems to be some triggering of the ComboBox_Change events when they haven't been changed - I suspect that too is because of changing autofilters on the materials sheet. It might not happen if all their .rowsources were on a hidden separate sheet.

Plenty more…

mdmackillop
08-20-2017, 08:06 AM
A first stab dealing with A-R only. Select the Combos and Listbox alternatively. There is an issue with DoFilter sub where the values are numeric

olegvolf
08-20-2017, 09:22 AM
Thank you very much!
What you meant by not using A and R

Thanks

olegvolf
08-20-2017, 11:27 AM
Hi Thanks

i am not succeeding to update the data through the filter or clear it
how can i do this?\

Thanks

olegvolf
08-20-2017, 11:31 AM
Hello and thank you
I can not apply your suggestion on the data it gives me an error
can you help to simulate

Thank you

olegvolf
08-20-2017, 12:04 PM
Hi
i tried it all
it not working
might be i do something not good

Can you help?
\
thanks

Lots to do, lots to do.
First, to answer your question, or rather part of it, you have a snippet of code:

For col = 1 To 5
col2 = Choose(col, 248, 250, 252, 254, 256)
.Range(.Cells(1, col), .Cells(LastRw, col)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, col2), Unique:=True
Next col

You can do something like:
For col = 1 To 5
col1 = Choose(col, 2, 5, 12, 14, 17) '<<these are the columns you want to include.
col2 = Choose(col, 248, 250, 252, 254, 256)
.Range(.Cells(1, col1), .Cells(LastRw, col1)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, col2), Unique:=True
Next col


Other things:
You have:
Set rSource = .Range(.Cells(2, 1), .Cells(LastRw, 5))
which clearly you can't use anymore.
Later on you have:
Set rSource = .Cells(2, 200).CurrentRegion
which you use later to reset the .rowsource of the listbox.
It's a completely different area.

Instead of using different areas at different times as a source for the listbox, I'd use the same(ish) area from the outset. If that area were on a completely separate (hidden) sheet that would be even better; it would likely help the following scenario with your userform:
If you fire it up, select HIGHC in the first combo and hit the Filter button, then select LOCK PIPE in the third combo, and hit the Filter button, you still have a big list in the listbox when there should be just one row visible. I haven't looked too closely but I think this is because
.Cells(1, 200).CurrentRegion.ClearContents is not fully erasing data because some rows are not visible - you'll have to check.

I'd put the comboxes' .rowsources on the same separate sheet too.

I'd update the comboboxes' .rowsources after every press of the Filter button too, so that (like doing an autofilter) the lists to choose from get shorter and shorter.

So instead of
Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)you could have
Set rSource = Intersect(Range.AutoFilter.Range.SpecialCells(xlCellTypeVisible), myColumns)where myColumns is something you've set up earlier with the likes of
Dim myColumns As Range
ColmArray = Array(2, 5, 12, 14, 17)
For Each c In ColmArray
If myColumns Is Nothing Then Set myColumns = Sheets("MATERIAL").Columns(c) Else Set myColumns = Union(myColumns, Sheets("MATERIAL").Columns(c))
Next c
'Application.Goto myColumns' just to show where the range is.


Likewise, instead of deleting .current region, delete entire columns - like you have already done in
Range("IM:IV").EntireColumn.ClearContents

There seems to be some triggering of the ComboBox_Change events when they haven't been changed - I suspect that too is because of changing autofilters on the materials sheet. It might not happen if all their .rowsources were on a hidden separate sheet.

Plenty more…

mdmackillop
08-20-2017, 01:02 PM
This should work with your first set if data in columns A-R. You don't explain how the other data is to be filtered.

i am not succeeding to update the data through the filter or clear it
I don't know what this means.

Hello and thank you
I can not apply your suggestion on the data it gives me an error
To which respondent are you replying. Please make this clear in your responses.

olegvolf
08-20-2017, 10:34 PM
Hello mdmackillop
Understood
I have one question
how can i edit selected values?
for example:"Bill Item Name" and he has a "Description"
How can i edit the description in the user form for it to be updated in the table?

Thank you
I selected a "Bill Item Name"

This should work with your first set if data in columns A-R. You don't explain how the other data is to be filtered.

I don't know what this means.

To which respondent are you replying. Please make this clear in your responses.

mdmackillop
08-21-2017, 12:32 AM
The purpose of this userform is to allow selective filtering of data; as per your posted question. Has it been answered? If not, let us know.
If you wish to edit data and change headers, that should be posted as a different question.

olegvolf
08-21-2017, 12:44 AM
thank you