Getting Userform to only scroll visible rows after filtering

[GopherUK]

Hi folks,

I am having a bit of a problem here.

I have a dataset that I have created a userform that will allow the data to be browsed and update where required. This is working 100% perfectly against a normal, unfiltered dataset. Using a method of picking up the record number from a text box on the form then fetching the data from the table via range("thetablename").Rows(recordnumber).

However, the data will require filtering at some point, then the filtered dataset will be browsed via the userform. The problem is that I cant seem to find a way of only getting it to pick up the (unhidden) rows left after the filter has been applied.

I know you can do .count etc against the table with just the range.SpecialCells(xlCellTypeVisible) method but I dont know of anyway of getting the code so when, for example, row 2 is selected by the user that the code will fetch row 2 from the filter range rather than just row 2 from the table as a whole. As far as I can see range.SpecialCells(xlCellTypeVisible).Rows(2) still seems happy to select hidden rows.

I can think of a few workarounds, such as exporting the filtered data to another data and have the userform browse from that, but I was wondering if there are any less clunky solutions?

Any ideas?

 

[moonfish]

How about an array of row numbers based on which rows are visible, and instead of referring to row numbers, refer to position in the array? Roughly:

declare dynamic array

for i from 1 to number_of_rows
    if range is visible
        increase array size by one
        add row number to array
    end if
next i

I'm terribly rusty, I'm sure anyone else can write it better than I can.

 

[GopherUK]

Thanks. That's one idea. Another I was thinking about was to create a table on a hidden sheet that is populate with each visible cell and it's respective ID. I can then perform a VLOOKUP on the table to get the row ID then use that to read from the dataset itself.