populate userform with filtered data

Hi all, after the very successful last thread im hoping to go one better!

I have a userform linked to some data - and basically want to show in the userform only the filtered data.

Normally the attached file has 19 records - however if you filter on colour you get 2 records.

on the VBA using the aValues = Worksheets("sheet1").Range("dataset").SpecialCells(xlCellTypeVisible) gives a zero value because the 'dataset' when visible starts on row 3 thus doesnt populate the box.

can anyone help so that the userform just populates the visible filtered data?

regards Simon

Simon

The reason the comboboxes aren't being populated is because you start the loop at 2.

You could change the 2 to LBound(avalues) but since the array hasn't been populated with all the filtered values it won't work either.

Try this.

Please Login or Register  to view this content.

Thanks Norie,
for some reason when the list isnt filtered - only the first line of the data comes up?
Also changes have to be made in the sub ABC routine too - which might not be too difficult, but if i apply this against a larger dataset (say with 30 cols of data to be summarised) i am not sure it will be feasible to code?
Simon

Simon

I've just realised a mistake - as well as looping through the Areas we need to loop through the rows in those Areas.

That's why when there was no filter only the first line showed.

In fact you would see the problem wherever the results of the filter left contiguous ranges, you would only see the first line.

Untested but try this.

Please Login or Register  to view this content.

Norie,

i think the easiest way to do it was to copy the filtered area to a new page, and direct the userform to populate from that area, once and then clear the data afterwards.

have attached the spreadsheet working. Thanks for your help though.

Simon

That's one way of doing it, and it it works that's the important thing.

Does the code I posted not work?