Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating a Filtered Range - I know its been done
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing/Manipulating Legend range for a Pivot Chart in XL2010 vsXL2003 and below. | Charts and Charting in Excel | |||
using countif in a filtered range | Excel Worksheet Functions | |||
Sum a filtered range | Excel Discussion (Misc queries) | |||
paste over a filtered range | Excel Discussion (Misc queries) | |||
filtered range | Excel Programming |