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).C ells.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" wrote in 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" wrote in message
...
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