Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining range for Advanced Filter
I'm having a difficult time on the range definition for and andvanced
filter callout, below is my code todate. The issue occurs on the Set oRange line, as I step through the macro it appear to highlight the appropriate cells but I get a "Run time Error #424 Object required", can anyone help me out. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 2/9/2006 by jreid ' ' Dim oRange As Object Range("AJ3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("DT3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("M3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("DU3").Select ActiveSheet.Paste Range("DT3").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("DT3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("DT3").Select Cells.Find(What:="1X", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select Set oRange = ActiveSheet.Range(Selection, Selection.End(xlDown)).Select oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _ Range("DW3"), Unique:=True Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet1").Select Range("B1").Select ActiveSheet.Paste Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining range for Advanced Filter
Hi Burl,
Try changing: Set oRange = ActiveSheet.Range(Selection, Selection.End(xlDown)).Select to: Set oRange = ActiveSheet.Range _ (Selection, Selection.End(xlDown)) --- Regards, Norman "burl_rfc" wrote in message ups.com... I'm having a difficult time on the range definition for and andvanced filter callout, below is my code todate. The issue occurs on the Set oRange line, as I step through the macro it appear to highlight the appropriate cells but I get a "Run time Error #424 Object required", can anyone help me out. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 2/9/2006 by jreid ' ' Dim oRange As Object Range("AJ3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("DT3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("M3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("DU3").Select ActiveSheet.Paste Range("DT3").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("DT3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("DT3").Select Cells.Find(What:="1X", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select Set oRange = ActiveSheet.Range(Selection, Selection.End(xlDown)).Select oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _ Range("DW3"), Unique:=True Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet1").Select Range("B1").Select ActiveSheet.Paste Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining range for Advanced Filter
Norm,
The solution worked great, I thank you greatly for helping out, this has been stumping me for a few days Again many thanks. But why is VBA so citical with the syntax, what did the underscore actually accomplish here (just to give me a beeter understanding). burl |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining range for Advanced Filter
Hi, Burl,
The underscore is merely cosmtic - it serves to break long lines of code. The real difference was the deletion of your Select. --- Regards, Norman "Norman Jones" wrote in message ... Hi Burl, Try changing: Set oRange = ActiveSheet.Range(Selection, Selection.End(xlDown)).Select to: Set oRange = ActiveSheet.Range _ (Selection, Selection.End(xlDown)) --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining range for Advanced Filter
Hi Burl,
But why is VBA so citical with the syntax, what did the underscore actually accomplish here (just to give me a beeter understanding). See my adjacent response - I inadvertently responded to the wrong strand of this thread! --- Regards, Norman "burl_rfc" wrote in message ps.com... Norm, The solution worked great, I thank you greatly for helping out, this has been stumping me for a few days Again many thanks. burl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced filter a range | Excel Worksheet Functions | |||
Advanced filter and Criteria Range | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Advanced Filter Range Selection in VB | Excel Programming | |||
Advanced Filter Range Selection in VB | Excel Programming |