Row State

[mikeymay]

Is there a way of establishing whether a row is currently filtered or not when filters are applied to a set of data?


Thanks

 

[crook_101]

Hi Mikey,

The link below should get you started:

http://www.mrexcel.com/forum/showthread.php?t=544409

Regards,
Ian

 

[cgcamal]

Hi mike,

I'm not sure if what you're asking, if so, normally when a row is filtered the row number change to blue as below:

Excel Workbook
	A
1	1
5	2
6	2
9	2
12	5
14	2
Sheet1

Excel 2010



Regards

 

[mikeymay]

Sorry, I meant is there a way using VBA to establish this.


Thanks

 

[JackDanIce]

Filter some data and then run this bit of code on it:

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

 

[mikeymay]

I really want to establish whether the activecell is filtered or not rather than is a filter applied to the data.

 

[mikeymay]

Think I have nailed this

Set rngRow = Range(Selection, Selection.Offset(0, 1))
            If rngRow.Rows(rngRow.Row).SpecialCells(xlCellTypeVisible).Count - 1 = 1 Then
                Do Until rngRow.Rows(rngRow.Row).SpecialCells(xlCellTypeVisible).Count - 1 = 1
                    Selection.Offset(1, 0).Select
                    Set rngRow = Range(Selection, Selection.Offset(0, 1))
                Loop
                Else

            
            End If

 

[cgcamal]

Try with:
Original input:

Excel Workbook
	A
1	1
2	1
3	1
4	1
5	2
6	2
7	3
8	3
9	2
10	1
11	3
12	5
13	1
14	2
Sheet1

Excel Workbook
	A	B
5	2	This cell is filtered
6	2	This cell is filtered
7	3	This cell is filtered
8	3	This cell is filtered
9	2	This cell is filtered
11	3	This cell is filtered
14	2	This cell is filtered
Excel 2010

#VALUE!

Excel 2010


Hope this helps,

Regards