Hi all,

I have what I think is a simple task, but I've been unable to implement it. I use a sheet that's essentially a part configurator. Part numbers are listed row by row, with associated characteristics in each column. You make your selection by making selections from the auto filter pull-down column by column, and eventually you've narrowed your choices down to the right, single part number. The problem is that you must move 30 to 40 columns over to see the part number, and I'm not at liberty to redesign the layout of the sheet.

My thought was that I could put an indicator cell in column 2 or 3, just above the row where the different auto filter headings are that would function as follows:

1) If the total number of non-hidden (i.e. not filtered) rows below the headers is >1, display the number of non-hidden rows to let the user know how much they've narrowed their choices.

2) If the total number of non-hidden rows below the headers is =1, display the value of the hidden row at a specified column. The column is always the same.

2a) If the total number of non-hidden rows below the headers is =1, and the text from the specified column equals some warning message (i.e. "No part currently available"), display that and change the cell background to red.

I figure this can be done in a function, and I know that the SUBTOTAL function, using the counta functionality, can be used to tell when there's just one non-hidden row. However, I'd like to add this functionality to many different sheets, and each one may have a different total number of rows. I'd like to make it a function so that the user doesn't have to activate the display, though I assume I could trigger a subroutine on the filtering action if I had to.

Here's what I have so far:

Please Login or Register  to view this content.
(I subtract 2 to account for things in the column which can't be moved)

I just don't know what to put in place of the "PART FOUND" value to display the contents of the first non-hidden row below row 15 (the header row). I found some examples of subs using Offset and Hidden to loop through each row until finding the last, non-hidden row, but couldn't get anything to work as a function.

Any help or advice would be greatly appreciated.