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

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

The function below is a modification of a function created by To
Ogilvy. I also found a similar function created by Norman Jones, but
couldn't get it to work properly. If anyone can help i'd greatl
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 referenceabl
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 T
ObjSpreadSheetC.Range(IndexRangeName(r)).Count

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

Nex

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