Hi Bart,

Think I agree with Dave, with a small range it would be faster.

OTH, you'd save some time if you use a permanent dummy sheet in an addin,
rather than creating/deleting a sheet each time.

Regards,
Peter T

"RB Smissaert" wrote in message
...
Haven't tested that, but I guess that will be a lot slower.

RBS


"Dave Peterson" wrote in message
...
One could loop through the visible rows of the original filtered data and
just add the values to an array.

This may be better (depending on the definition of better <vbg).

On 08/16/2010 02:58, RB Smissaert wrote:
Hi Jim,

As you say, interesting, but not usable.
Peculiar that there is no better way to get the filtered
data other than copying to another sheet.

RBS


"Jim Cone" wrote in message
...
Not a real solution, but it was an interesting exercise...
'--
Sub TestIt()
Dim vFilterRange As Variant
Dim strFilterAddress As String
Dim x As Long
Dim y As Long

'The range address has a length limitation of ~ 256 characters.
'So the following only works on a small filtered range.
'You must specify the filtered column number.

strFilterAddress =
ActiveSheet.AutoFilter.Range.Columns(1).SpecialCel ls(xlCellTypeVisible).Address
vFilterRange = VBA.Split(strFilterAddress, ",", -1, vbBinaryCompare)

x = LBound(vFilterRange, 1)
y = UBound(vFilterRange, 1)
MsgBox "Lower bound is: " & x & vbCr & "Upper bound is: " & y

vFilterRange = VBA.Join(vFilterRange, ":")
vFilterRange = VBA.Split(vFilterRange, ":", -1, vbBinaryCompare)

x = LBound(vFilterRange, 1)
y = UBound(vFilterRange, 1)
MsgBox "Lower bound is: " & x & vbCr & "Upper bound is: " & y
End Sub

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

.
.
.

"RB Smissaert"
wrote in message
...
Just some further streamlining of this code.
Still not found a better way to handle this.

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

Dim shNew As Worksheet

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

Set shNew = ActiveWorkbook.Sheets.Add

rngFilter.Copy shNew.Cells(1)

With shNew
If bOmitHeader Then
getFilteredRows = .Range(.Cells(2, 1), .Cells(2,
1).SpecialCells(xlLastCell))
Else
getFilteredRows = .UsedRange
End If

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
End Function

RBS





--
Dave Peterson