Hi,
I have an autofilter and I want to determine the row number of the start
and end of the filtered data. I thought initially I could use offset
but unfortunately this returns row 2 even although it is hidden (as it
does not match the criteria specified).

I've tried using SendKeys, which looked ideal, to press the equivalent
of the down arrow but it's affecting the module not the worksheet! Can
anyone help me to workout the row number?

Best Regards,

CalumMurdo Kennedy

CalumMurdo,

I am sure there are more efficient ways of doing it, but this appears to
return the right answer.

Sub DetectFilteredRows()
'assumes autofilter starts at a1 with a label header, data starts at a2, and
filter is applied
Dim rngFilter As Range
With Sheets(1)
Set rngFilter = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
MsgBox "Last filtered row is at " & rngFilter(rngFilter.Rows.Count,
1).Row
MsgBox "First filtered row is at " &
rngFilter.SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com

"CMK" wrote in message
...
Hi,
I have an autofilter and I want to determine the row number of the start
and end of the filtered data. I thought initially I could use offset but
unfortunately this returns row 2 even although it is hidden (as it does
not match the criteria specified).

I've tried using SendKeys, which looked ideal, to press the equivalent of
the down arrow but it's affecting the module not the worksheet! Can
anyone help me to workout the row number?

Best Regards,

CalumMurdo Kennedy

Hi Robin,

Robin Hammond wrote:
CalumMurdo,

I am sure there are more efficient ways of doing it, but this appears to
return the right answer.

Sub DetectFilteredRows()
'assumes autofilter starts at a1 with a label header, data starts at a2, and
filter is applied
Dim rngFilter As Range
With Sheets(1)
Set rngFilter = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
MsgBox "Last filtered row is at " & rngFilter(rngFilter.Rows.Count,
1).Row
MsgBox "First filtered row is at " &
rngFilter.SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
End With
End Sub

That works perfectly thanks. Don't know what was up with the SendKeys
but this works fine and dandy, thanks.

CalumMurdo


Robin Hammond
www.enhanceddatasystems.com

"CMK" wrote in message
...

Hi,
I have an autofilter and I want to determine the row number of the start
and end of the filtered data. I thought initially I could use offset but
unfortunately this returns row 2 even although it is hidden (as it does
not match the criteria specified).

I've tried using SendKeys, which looked ideal, to press the equivalent of
the down arrow but it's affecting the module not the worksheet! Can
anyone help me to workout the row number?

Best Regards,

CalumMurdo Kennedy