Is there any better way (faster mainly or neater) to get the values of a
filtered range into an array than copying that filtered range to a different
sheet and then getting the pasted values into an array?
Currently I use this code, but I have a feeling there must be better way,
avoiding the copy:

Function getFilteredRows(ByRef rngFilter As Range, _
Optional bHeader As Boolean, _
Optional oSheet As Worksheet) As Variant

Dim shNew As Worksheet
Dim lRowCount As Long
Dim lColCount As Long
Dim arr

If oSheet Is Nothing Then
Set oSheet = ActiveSheet
End If

If oSheet.FilterMode = False Then
'early exit if the sheet has no active filter
'--------------------------------------------
getFilteredRows = rngFilter
Exit Function
End If

Application.ScreenUpdating = False

lColCount = rngFilter.Columns.Count
lRowCount = rngFilter.SpecialCells(xlCellTypeVisible).Cells.Co unt \
lColCount

For Each shNew In ActiveWorkbook.Worksheets
If shNew.Name = "ZYQYZ" Then
shNew.Delete
End If
Next shNew

Set shNew = ActiveWorkbook.Sheets.Add
shNew.Name = "ZYQYZ"

rngFilter.Copy Sheets("ZYQYZ").Cells(1)

With Sheets("ZYQYZ")
If bHeader Then
arr = .Range(.Cells(2, 1), .Cells(lRowCount, lColCount))
Else
arr = .Range(.Cells(1), .Cells(lRowCount, lColCount))
End If
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Application.ScreenUpdating = True

getFilteredRows = arr

End Function


One way would be just looping through the filtered range and only put values
in the array
of rows that are not hidden, but that is a lot slower than the above code.


RBS