Excel_Macro_References/GetFilteredRangeTopBtmRows at master · fabiod76/Excel_Macro_References · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Public Function GetFiltRgTopRow() As Long

'this function returns the top row number of a filtered list

Dim HeaderRow As Long
Dim LastFilterRow As Long

On Error GoTo NoFilterOnSheet
With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFiltRgTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFiltRgTopRow = LastFilterRow + 1 Then GetFiltRgTopRow = 0
End With

NoFilterOnSheet:

End Function

Public Function GetFiltRgBtmRow() As Long

'this function returns the bottom row number of a filtered list

Dim HeaderRow As Long
Dim LastFilterRow As Long
Dim Addresses() As String

On Error GoTo NoFilterOnSheet
With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    Addresses = Split(.Range((HeaderRow + 1) & ":" & LastFilterRow).SpecialCells(xlCellTypeVisible).Address, "$")
    GetFiltRgBtmRow = Addresses(UBound(Addresses))
End With

NoFilterOnSheet:

End Function