CODEWITHSUNDEEP
excelvba
PhilNBlanks
PhilNBlanks

Reputation: 125

How to find row number of previous visible row

How can I color every other "group" of rows in filtered data?

Something like =MOD(SUBTOTAL(103,$A$1:$A2),2)=1 but for groups which will return TRUE AND FALSE in a helper column that is used to color on not color the row.

I have created a helper column which indicates whether or not to shade the row IF THE DATA IS NOT FILTRED but it obviously fails once a filter is applied.

Helper column formula in column T: =IF(ROW()=2,FALSE,IF(Q2=Q1,T1,NOT(T1)))

enter image description here

However if I filter to show only the Miami and Chicago locations I get this:

enter image description here

My helper formula is basing its results on the previous rows Job# and NOT the Previous VISIBLE row's Job#.

Is there some way to write a helper formula or macro that will only look at visible rows??

Upvotes: 1

Views: 418

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use LOOKUP with SUBTOTAL to find the last visible row:

LOOKUP(2,1/(SUBTOTAL(3,OFFSET($Q$1,(ROW($Q$1:$Q1)-1),0,1,1))>0),ROW($Q$1:$Q1))

so:

=IF(LOOKUP(2,1/(SUBTOTAL(3,OFFSET($Q$1,(ROW($Q$1:$Q1)-1),0,1,1))>0),ROW($Q$1:$Q1))=1,FALSE,IF(INDEX(Q:Q,LOOKUP(2,1/(SUBTOTAL(3,OFFSET($Q$1,(ROW($Q$1:$Q1)-1),0,1,1))>0),ROW($Q$1:$Q1)))=Q2,INDEX(T:T,LOOKUP(2,1/(SUBTOTAL(3,OFFSET($Q$1,(ROW($Q$1:$Q1)-1),0,1,1))>0),ROW($Q$1:$Q1))),NOT(INDEX(T:T,LOOKUP(2,1/(SUBTOTAL(3,OFFSET($Q$1,(ROW($Q$1:$Q1)-1),0,1,1))>0),ROW($Q$1:$Q1))))))

When not visible it will error but it is not seen:

enter image description here

enter image description here

Upvotes: 1

Related Questions