Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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/



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default 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/



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Manipulating a Filtered Range - I know its been done

Thanks guys i finally got it workin

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing/Manipulating Legend range for a Pivot Chart in XL2010 vsXL2003 and below. Grumpy Aero Guy Charts and Charting in Excel 0 March 26th 11 11:31 PM
using countif in a filtered range Mr E Excel Worksheet Functions 12 December 9th 09 10:19 PM
Sum a filtered range Peter Excel Discussion (Misc queries) 2 April 30th 09 07:57 AM
paste over a filtered range freddie2711 Excel Discussion (Misc queries) 8 April 27th 05 01:20 PM
filtered range MarkJ Excel Programming 7 June 26th 04 10:49 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"