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

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 :
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

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

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

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


"isabelle" wrote in message ...
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

On Sat, 23 Feb 2013 10:29:33 -0000, "Tim Childs" wrote:

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.

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 :
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

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

"isabelle" wrote in message ...
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 :
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

Hi Ron

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

best wishes

Tim

"Ron Rosenfeld" wrote in message
...
On Sat, 23 Feb 2013 10:29:33 -0000, "Tim Childs"
wrote:

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.

On Sat, 23 Feb 2013 21:52:25 -0000, "Tim Childs" wrote:

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.

thx for those additional observations, Tim

"Ron Rosenfeld" wrote in message
...

On Sat, 23 Feb 2013 21:52:25 -0000, "Tim Childs" wrote:

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.