Hi Nigel,
One way::
'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range
Dim rCell As Range
Dim rFirst As Range
Dim rLast As Range
Dim i As Long
Set WB = Workbooks("myBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet3") '<<==== CHANGE
With SH
If Not .AutoFilterMode Then
MsgBox Prompt:="No Autofilter found", _
Buttons:=vbCritical, _
Title:="No Autofilter"
Exit Sub
End If
Set Rng = .AutoFilter.Range.Columns(1)
With Rng
Set Rng = .Offset(1).Resize(.Rows.Count - 1)
End With
On Error Resume Next
Set Rng2 = Rng.SpecialCells(xlVisible)
On Error GoTo 0
End With
If Rng2 Is Nothing Then
MsgBox Prompt:=" There are no filtered rows"
Exit Sub
End If
For Each rCell In Rng2.Cells
i = i + 1
If rFirst Is Nothing Then
Set rFirst = rCell
End If
Set rLast = rCell
Next rCell
MsgBox Prompt:="The Auto filter contains " _
& i & " visible data rows" _
& vbNewLine _
& "The first visible cell is " _
& rFirst.Address(0, 0) _
& vbNewLine _
& "The last visible cell is " _
& rLast.Address(0, 0), _
Buttons:=vbInformation, _
Title:="Autofilter Report"
End Sub
'<<=============
---
Regards.
Norman
"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:14131CC9-3587-45D7-8408-(E-Mail Removed)...
> Hi All
> After I apply an autofilter, how can I determine the first visible row
> number and last visible row number of the filtered list ?
>
> Cheers
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
Similar Threads
