how do i identify first and last row once filter applied

having applied an auto filter in vba how do i indentify the first and last
row of the result also is it possible to read the number of rows. i assume
this is held somewhere as it is used in the status bar message

All rows would be

rng.Rows.Count

where rng is the original range being filtered,

visible rows would be

Rng.SpecialCells(xlCellTypeVisible).Count

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
> having applied an auto filter in vba how do i indentify the first and last
> row of the result also is it possible to read the number of rows. i assume
> this is held somewhere as it is used in the status bar message

hi bob

sorry its taken a while to get back to you

i am applying a filter to 3 columns and would like to know how many records
9rows) i end up with

would rng be the range before i apply the filters or after the second and
before the third

"Bob Phillips" wrote:

> All rows would be
>
> rng.Rows.Count
>
> where rng is the original range being filtered,
>
> visible rows would be
>
> Rng.SpecialCells(xlCellTypeVisible).Count
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
> > having applied an auto filter in vba how do i indentify the first and last
> > row of the result also is it possible to read the number of rows. i assume
> > this is held somewhere as it is used in the status bar message
>
>
>

hi bob

have tried this and it works but "Rng.SpecialCells(xlCellTypeVisible).Count
" counts cell my spreadsheet has 200 columns and 22500 rows

ive tried adding ".rows" but i then get a result of 1

i appreciate i could divide the result by the number of columns but then i
got to deal with fractions as well

any ideas

"Bob Phillips" wrote:

> All rows would be
>
> rng.Rows.Count
>
> where rng is the original range being filtered,
>
> visible rows would be
>
> Rng.SpecialCells(xlCellTypeVisible).Count
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
> > having applied an auto filter in vba how do i indentify the first and last
> > row of the result also is it possible to read the number of rows. i assume
> > this is held somewhere as it is used in the status bar message
>
>
>

Pete,

I'm not sure how you set rng, but for this example select just one column of the list prior to
filtering that column...

Sub TryNow()
Dim Rng As Range
Dim myAreas As Integer
Dim myCells As Integer

Set Rng = Selection
myAreas = Rng.SpecialCells(xlCellTypeVisible).Areas.Count
myCells = Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells.Count
MsgBox "First row of data is " & _
IIf(Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count = 1, _
Rng.SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row, _
Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells(2).Row)
MsgBox "Last row of data is " & _
Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells(myCells).Row
MsgBox "Total rows of data is " & Rng.SpecialCells(xlCellTypeVisible).Count - 1
End Sub

HTH,
Bernie
MS Excel MVP


"pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
news:BBC1D8AD-EF59-4E40-A618-E533B20E8313@microsoft.com...
> hi bob
>
> have tried this and it works but "Rng.SpecialCells(xlCellTypeVisible).Count
> " counts cell my spreadsheet has 200 columns and 22500 rows
>
> ive tried adding ".rows" but i then get a result of 1
>
> i appreciate i could divide the result by the number of columns but then i
> got to deal with fractions as well
>
> any ideas
>
> "Bob Phillips" wrote:
>
>> All rows would be
>>
>> rng.Rows.Count
>>
>> where rng is the original range being filtered,
>>
>> visible rows would be
>>
>> Rng.SpecialCells(xlCellTypeVisible).Count
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
>> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
>> > having applied an auto filter in vba how do i indentify the first and last
>> > row of the result also is it possible to read the number of rows. i assume
>> > this is held somewhere as it is used in the status bar message
>>
>>
>>

Pete,

I'm not sure how you set rng, but for this example select just one column of the list prior to
filtering that column...

Sub TryNow()
Dim Rng As Range
Dim myAreas As Integer
Dim myCells As Integer

Set Rng = Selection
myAreas = Rng.SpecialCells(xlCellTypeVisible).Areas.Count
myCells = Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells.Count
MsgBox "First row of data is " & _
IIf(Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count = 1, _
Rng.SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row, _
Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells(2).Row)
MsgBox "Last row of data is " & _
Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells(myCells).Row
MsgBox "Total rows of data is " & Rng.SpecialCells(xlCellTypeVisible).Count - 1
End Sub

HTH,
Bernie
MS Excel MVP


"pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
news:BBC1D8AD-EF59-4E40-A618-E533B20E8313@microsoft.com...
> hi bob
>
> have tried this and it works but "Rng.SpecialCells(xlCellTypeVisible).Count
> " counts cell my spreadsheet has 200 columns and 22500 rows
>
> ive tried adding ".rows" but i then get a result of 1
>
> i appreciate i could divide the result by the number of columns but then i
> got to deal with fractions as well
>
> any ideas
>
> "Bob Phillips" wrote:
>
>> All rows would be
>>
>> rng.Rows.Count
>>
>> where rng is the original range being filtered,
>>
>> visible rows would be
>>
>> Rng.SpecialCells(xlCellTypeVisible).Count
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
>> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
>> > having applied an auto filter in vba how do i indentify the first and last
>> > row of the result also is it possible to read the number of rows. i assume
>> > this is held somewhere as it is used in the status bar message
>>
>>
>>

Pete,

I'm not sure how you set rng, but for this example select just one column of the list prior to
filtering that column...

Sub TryNow()
Dim Rng As Range
Dim myAreas As Integer
Dim myCells As Integer

Set Rng = Selection
myAreas = Rng.SpecialCells(xlCellTypeVisible).Areas.Count
myCells = Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells.Count
MsgBox "First row of data is " & _
IIf(Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count = 1, _
Rng.SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row, _
Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells(2).Row)
MsgBox "Last row of data is " & _
Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells(myCells).Row
MsgBox "Total rows of data is " & Rng.SpecialCells(xlCellTypeVisible).Count - 1
End Sub

HTH,
Bernie
MS Excel MVP


"pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
news:BBC1D8AD-EF59-4E40-A618-E533B20E8313@microsoft.com...
> hi bob
>
> have tried this and it works but "Rng.SpecialCells(xlCellTypeVisible).Count
> " counts cell my spreadsheet has 200 columns and 22500 rows
>
> ive tried adding ".rows" but i then get a result of 1
>
> i appreciate i could divide the result by the number of columns but then i
> got to deal with fractions as well
>
> any ideas
>
> "Bob Phillips" wrote:
>
>> All rows would be
>>
>> rng.Rows.Count
>>
>> where rng is the original range being filtered,
>>
>> visible rows would be
>>
>> Rng.SpecialCells(xlCellTypeVisible).Count
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
>> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
>> > having applied an auto filter in vba how do i indentify the first and last
>> > row of the result also is it possible to read the number of rows. i assume
>> > this is held somewhere as it is used in the status bar message
>>
>>
>>

Pete,

I'm not sure how you set rng, but for this example select just one column of the list prior to
filtering that column...

Sub TryNow()
Dim Rng As Range
Dim myAreas As Integer
Dim myCells As Integer

Set Rng = Selection
myAreas = Rng.SpecialCells(xlCellTypeVisible).Areas.Count
myCells = Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells.Count
MsgBox "First row of data is " & _
IIf(Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count = 1, _
Rng.SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row, _
Rng.SpecialCells(xlCellTypeVisible).Areas(1).Cells(2).Row)
MsgBox "Last row of data is " & _
Rng.SpecialCells(xlCellTypeVisible).Areas(myAreas).Cells(myCells).Row
MsgBox "Total rows of data is " & Rng.SpecialCells(xlCellTypeVisible).Count - 1
End Sub

HTH,
Bernie
MS Excel MVP


"pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
news:BBC1D8AD-EF59-4E40-A618-E533B20E8313@microsoft.com...
> hi bob
>
> have tried this and it works but "Rng.SpecialCells(xlCellTypeVisible).Count
> " counts cell my spreadsheet has 200 columns and 22500 rows
>
> ive tried adding ".rows" but i then get a result of 1
>
> i appreciate i could divide the result by the number of columns but then i
> got to deal with fractions as well
>
> any ideas
>
> "Bob Phillips" wrote:
>
>> All rows would be
>>
>> rng.Rows.Count
>>
>> where rng is the original range being filtered,
>>
>> visible rows would be
>>
>> Rng.SpecialCells(xlCellTypeVisible).Count
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "pete the greek" <petethegreek@discussions.microsoft.com> wrote in message
>> news:7A95F88F-D232-4580-A280-21FA69ADBE59@microsoft.com...
>> > having applied an auto filter in vba how do i indentify the first and last
>> > row of the result also is it possible to read the number of rows. i assume
>> > this is held somewhere as it is used in the status bar message
>>
>>
>>