Autofilter Range

[Nigel]

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

 

[ND Pard]

Open your VBA editor (Alt & F11), insert a Module, copy and paste the macro
below.

Go Back to your spreadsheet and place your cursor in any of the Filtered
cells.

Run the macro (Alt & F8).

Good Luck.

Sub FilteredData_1st_n_Last_Rows()
Selection.CurrentRegion.Select
Range(ActiveCell.Offset(1), ActiveCell.Offset _
(Selection.Rows.Count - 1, Selection.Columns.Count - 1)).Select
Do While ActiveCell.Height = 0
ActiveCell.Offset(1).Select
Loop
MsgBox "First Row = " & ActiveCell.Row
Selection.CurrentRegion.Select
MsgBox "Last Row = " & Selection.SpecialCells(xlCellTypeLastCell).Row
End Sub

 

[Nigel]

Thanks for the reply, I was rather hoping to avoid selecting cells and to
scan the filtered list looking for hidden=false rows. My list could be very
large (>100k rows) and this approach would be very slow.

Thanks again

--

Regards,
Nigel
(e-mail address removed)

 

[ND Pard]

Well, there's always more than one way to skin a cat.

You could try this, add a new column (say to the far right) of your data.
In each cell except the header row enter something, say: =row()

Do NOT put a header on this column.

Filter your data including the last column and then try this macro:

Sub FilteredData_1st_n_Last_Rows()
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeLastCell).Select
MsgBox "Last Row = " & ActiveCell.Row
MsgBox "First Row = " & ActiveCell.End(xlUp).Row
End Sub

This should work; at least it did when I tried it.

Good Luck.

 

[Norman Jones]

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
'<<=============

 

[Nigel]

Neat trick using a helper column, I will give it ago. Thanks

--

Regards,
Nigel
(e-mail address removed)

 

[Nigel]

Thanks, nice idea I will try it out.

--

Regards,
Nigel
(e-mail address removed)