1

How can I get a reference to the first visible cell above the active one after the data in a table has been filtered.

NOTE: I don't need this for summing up the data above. I know how to do that on filtered data.

This is more complicated. What I'm actually making is a function which would allow me to alternate row colors of duplicate data similar to (Alternate Row Colors of Duplicate Data in Excel) but it needs to be able to work on filtered data.

Community
  • 1
SilverWarior
  • 156

1 Answers1

1

It can be done by adding another column with Subtotal Function. Assumption:

1 - First Row is header row

2 - New Column added is "A" to the left of Data

3 - Filter is applied on Column "C".

A2 =SUBTOTAL(3,$C$2:C2)
A3 =SUBTOTAL(3,$C$2:C3)
...

When you apply a filter values in Column "A" will be updated with 1st visible row showing "1" in Col-A even though its actual row number may be 1000.

I have earlier used it successfully. Only limitation is that it is calculation intensive, so use with caution.

Shivam7898
  • 61