Visible Cells Only, Autofilter

Hello, simple problem, but apparently very difficult to solve gracefully.

1. I have an excel worksheet with several rows of data (records)
2. I've enabled autofilter in VBA and filtering out rows based on filter criteria
3. I'm trying to capture a value in column H for a visible cell only. E.g., when the filter is applied, all rows but 1 go away, that 1 row, I need the value that is in the remaining visible column H
4. my plan is to use range("h500").end(xlup).select (total row data is 400 rows long)
5. problem is, the code works, but xlups to the last row in the record (row 400) even through the row is autofiltered. (no visible)

I've searched the web, lots of examples, but they all seem to complicated. I've played around with special cells (xlcelltypevisible), but no luck there.

Appreciate the support.

Thx

You'd be far better off adding a column to your data. If your data were in A1:X1000,

Y2:

Formula:

Please Login or Register  to view this content.

Fill Y2 down into Y3:Y1000.

If there were only one visible row, the col Y value in that row would be 1, and all other col Y values would be 0.

Alternatively, if the values you want are numeric, consider using Application.WorksheetFunction.Aggregate. The sum of a single visible cell would be that single cell's value.