Manipulating a Filtered Range - I know its been done
 

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

Manipulating a Filtered Range - I know its been done
 

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/

Manipulating a Filtered Range - I know its been done
 

if i were to traverse through the range through rng1 like this

for x=0 to rng1.count
msgbox rng1(x)
next

would it show only the visible cells?

because i have tried that and it doesn't work. is there anyway to g
through the a column of a filtered range row by row

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

Manipulating a Filtered Range - I know its been done
 

Zest,

Dim myCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A10")

For Each myCell In Rng1.SpecialCells(xlCellTypeVisible)
MsgBox myCell.Value
Next myCell

HTH,
Bernie
MS Excel MVP

"zestpt " wrote in message
...
if i were to traverse through the range through rng1 like this

for x=0 to rng1.count
msgbox rng1(x)
next

would it show only the visible cells?

because i have tried that and it doesn't work. is there anyway to go
through the a column of a filtered range row by row?


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

Manipulating a Filtered Range - I know its been done
 

Thanks guys i finally got it workin

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