Public Function FilteredAsRange(col As String, rngx As Range, _
CurrentWSheet As Object, RangeWSheet As Object)
'makes the selected col of wksheet filtered range as referenceable
range
Dim rng As Range
Dim rng1 as Range
Dim icol as String
RangeWSheet.Activate

icol = Range(Col).Column

Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol)).Cells
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count)-1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0

if not rng1 is nothing then
rng1.entireRow.copy Destination:= _
CurrentWSheet.Range("A1")
Else
msgbox "No visible rows"
End If
End Function

Don't really know you layout or exactly what you want to do, but perhaps the
above will owrk.
for instance, I don't know what role rngx is supposed to play. As written,
it assumes the filter is already applied and some rows are hidden.

It is fine to make this a function, but you can't use it in a spread sheet
like

=FilteredAsRange( "I1", PCR_AMTRange, Sheet1, Sheet2)

Also, the source and destination sheets should be different unless you
identify a destination range that is outside the filtered data.
--
Regards,
Tom Ogilvy

"zestpt " wrote in message
...
I'm trying to assign a filtered range to a range variable so i can
manipulate it later on. The problem that i keep having is that as i am
traversing through the range and copying values over to a different
spreadsheet or a different range, values that weren't filtered and
should be hidden are copied over.

I've looked at the messageboard and found isntances where people were
having a similar problem and i tried to use their code but it didn't
really work out.

The function below is a modification of a function created by Tom
Ogilvy. I also found a similar function created by Norman Jones, but i
couldn't get it to work properly. If anyone can help i'd greatly
apprciate it.

Thanks,
Z


Here is the function:
----------------------------------------------
Public Function FilteredAsRange(col As String, rngx As Range,
CurrentWSheet As Object, RangeWSheet As Object)
'makes the selected col of wksheet filtered range as referenceable
range
Dim rng As Range
Dim icol As Long

RangeWSheet.Activate

'allows for the filtered range to referred to as rng1
Range(col).Select 'move to column where filtered range is
icol = ActiveCell.Column

'If AutoFilterMode = False Then
'Range(ActiveCell.Address).AutoFilter
'End If

Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rngx = rng.SpecialCells(xlVisible)
On Error GoTo 0

CurrentWSheet.Activate

End Function
----------------------------------------------
Example of the function being called:

FilteredAsRange "I1", PCR_AMTRange, ActiveSheet, ActiveSheet

----------------------------------------------
Example of the filtered range being used:
For i = 1 To
ObjSpreadSheetC.Range(IndexRangeName(r)).Count

ObjSpreadSheetC.Range(IndexRangeName(r))(i) =
PCR_AMTRange(i)

Next


---
Message posted from http://www.ExcelForum.com/