Code Snippet - Data Filter

[Tim Childs]

Hi

If there is a worksheet with a table of data and other data beneath that, is
there a property associated with the Data Filter method that will return the
last row included in the filtered data i.e. the last row in scope of the
filtering, so it is not dependent on the current criteria set, just on the
basis of where the filter is including as being in scope. For the avoidance
of doubt, it is the last row the user included when the filter area was
created initially.

Many thanks

Tim

 

[isabelle]

hi Tim,

Sub Filter_Row()
Dim plg As Range, X As Variant, x1 As String, x2 As Variant, xx As String
Set plg =
ActiveSheet.Range("_filterdatabase").SpecialCells(xlCellTypeVisible)
S = Split(plg.Address, ",")
If UBound(S) = 0 Then
x1 = Range(Cells(Range(S(0)).Row + 1,
Range(S(0)).Column).Address).Row ' first row
x2 = Split(S(0), ":")
xx = Range(x2(UBound(x2))).Row ' last row
Else
x1 = Range(S(1)).Row ' first row
xx = Range(S(UBound(S))).Row ' last row
End If
End Sub


isabelle

Le 2013-02-23 05:29, Tim Childs a écrit :

 

[isabelle]

correction,

Sub Filter_first_last()
Dim plg As Range, x As Variant, pr As String, dr As String
Set plg =
ActiveSheet.Range("_filterdatabase").SpecialCells(xlCellTypeVisible)
x = Split(plg.Address, ",")
If Range(x(0)).Rows.Count > 1 Then
pr = Range(x(0)).Rows(2).Row 'first row
dr = Range(x(UBound(x))).Row 'last row
Else
pr = Range(x(1)).Row 'first row
dr = Range(x(UBound(x))).Row 'last row
End If
MsgBox "Fisrt: " & pr & " Last: " & dr
End Sub


isabelle

 

[isabelle]

Sorry, this time i think i've thought of everything,
i had forgotten if the filter returns a single line, line 2

Sub Filter_first_last2()
Dim plg As Range, x As Variant, first As String, last As String
Set plg =
ActiveSheet.Range("_filterdatabase").SpecialCells(xlCellTypeVisible)

x = Split(plg.Address, ",")

last = Range(x(UBound(x))).Rows(Range(x(UBound(x))).Rows.Count).Row
'last row

If Range(x(0)).Rows.Count > 1 Then
first = Range(x(0)).Rows(2).Row 'first row
Else
first = Range(x(1)).Row 'first row
End If

MsgBox "first: " & first & vbCrLf & "Last: " & last
End Sub

isabelle

 

[Tim Childs]

Hi Isabelle

Thanks for response. Did you test it because for me it provided the correct
answer for the first filtered row but not the last..

Look forward to hearing from you

Tim

 

[Ron Rosenfeld]

Hi

If there is a worksheet with a table of data and other data beneath that, is
there a property associated with the Data Filter method that will return the
last row included in the filtered data i.e. the last row in scope of the
filtering, so it is not dependent on the current criteria set, just on the
basis of where the filter is including as being in scope. For the avoidance
of doubt, it is the last row the user included when the filter area was
created initially.

Many thanks

Tim

If I understand you correctly, at least in Excel 2007+, the range property of the autofilter object will return the range to which the autofilter applies. However, empirical testing shows that if there are lines added immediately below this range, they will be added to the autofilter.range.

If the "other data beneath" is separated by at least one blank row, then the Range property should give you what you want.

 

[isabelle]

Sub Filter_first_last2()
Dim plg As Range, x As Variant, first As String, last As String
Set plg =
ActiveSheet.Range("_filterdatabase").SpecialCells(xlCellTypeVisible)

x = Split(plg.Address, ",")

last = Range(x(UBound(x))).Rows(Range(x(UBound(x))).Rows.Count).Row
'last row

If Range(x(0)).Rows.Count > 1 Then
first = Range(x(0)).Rows(2).Row 'first row
Else
first = Range(x(1)).Row 'first row
End If

MsgBox "first: " & first & vbCrLf & "Last: " & last
End Sub


http://cjoint.com/?CBxvf5eIikQ

isabelle

Le 2013-02-23 14:04, Tim Childs a écrit :

 

[Tim Childs]

Hi Isabelle

Thanks for that analysis and the link - very helpful

I think that my request was the simple one about where the filter range
ended, as Ron adds it seems that this is elastic if data is added on which
surprised me.

Best wishes

Tim

 

[Tim Childs]

Hi Ron

thanks for that - intersed to see that filter range expands if cells add in
adjacent area

best wishes

Tim

 

[Ron Rosenfeld]

Hi Ron

thanks for that - intersed to see that filter range expands if cells add in
adjacent area

best wishes

Tim

I was surprised, myself, to find that. When trying to answer your question, I discovered that phenomenon.

It didn't expand when one adds columns, however.

And there are some other patterns that are interesting.

I don't know if there is any setting in Excel that will change this behavior -- haven't experimented.

 

[Tim Childs]

thx for those additional observations, Tim

"Ron Rosenfeld" wrote in message

Hi Ron

thanks for that - intersed to see that filter range expands if cells add in
adjacent area

best wishes

Tim

I was surprised, myself, to find that. When trying to answer your question,
I discovered that phenomenon.

It didn't expand when one adds columns, however.

And there are some other patterns that are interesting.

I don't know if there is any setting in Excel that will change this
behavior -- haven't experimented.