Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranging
I trying to program an advanced filter in VBA and copy the unique results to
another location. I recorded the code below to get the basics. However, I run into a problem when the source information changes size. In the example below the source range is row 6-14 because that is what the macro recorded for this instance. Given that the first row remains fixed, how can I get the address for the last row into the code so that it works no matter how many rows of information there are? And while I am at it, the target range is on another worksheet, so could you help me out with the syntax for that as well. And, since I can't post questions from work, is it possible to select a range and assign it to a variable like [Source] and [Target] and put those into the filter code. Range("C6").Select Range(Selection, Selection.End(xlDown)).Select Range("C6:C14").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "E6"), Unique:=True As always, any help would be greatly appreciated -- Michael Conroy Stamford, CT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranging
Try this out.
Sub tt() Dim src As Excel.Worksheet, rngFilter As Range Set src = ThisWorkbook.Sheets("Sheet1") Set rngFilter = src.Range(src.Range("C6"), src.Range("C6").End(xlDown)) rngFilter.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=src.Range("E6"), Unique:=True End Sub Tim "Michael Conroy" wrote in message ... I trying to program an advanced filter in VBA and copy the unique results to another location. I recorded the code below to get the basics. However, I run into a problem when the source information changes size. In the example below the source range is row 6-14 because that is what the macro recorded for this instance. Given that the first row remains fixed, how can I get the address for the last row into the code so that it works no matter how many rows of information there are? And while I am at it, the target range is on another worksheet, so could you help me out with the syntax for that as well. And, since I can't post questions from work, is it possible to select a range and assign it to a variable like [Source] and [Target] and put those into the filter code. Range("C6").Select Range(Selection, Selection.End(xlDown)).Select Range("C6:C14").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "E6"), Unique:=True As always, any help would be greatly appreciated -- Michael Conroy Stamford, CT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranging
Hello Michael,
Try the following. Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. Sub AdvancedFiltering() Dim rngSource As Range Dim rngTarget As Range 'Assign source to a range variable With Sheets("Sheet1") Set rngSource = .Range(.Cells(6, "C"), _ .Cells(6, "C").End(xlDown)) End With 'Assign target range on another 'worksheet to a range variable With Sheets("Sheet2") Set rngTarget = .Range("E6") End With 'Advanced filtering of source to 'Target on another worksheet rngSource.AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=rngTarget, _ Unique:=True End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranging
Hi Tim,
The OP specified "the target range is on another worksheet, so could you help me out with the syntax for that as well". -- Regards, OssieMac "Tim Williams" wrote: Try this out. Sub tt() Dim src As Excel.Worksheet, rngFilter As Range Set src = ThisWorkbook.Sheets("Sheet1") Set rngFilter = src.Range(src.Range("C6"), src.Range("C6").End(xlDown)) rngFilter.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=src.Range("E6"), Unique:=True End Sub Tim "Michael Conroy" wrote in message ... I trying to program an advanced filter in VBA and copy the unique results to another location. I recorded the code below to get the basics. However, I run into a problem when the source information changes size. In the example below the source range is row 6-14 because that is what the macro recorded for this instance. Given that the first row remains fixed, how can I get the address for the last row into the code so that it works no matter how many rows of information there are? And while I am at it, the target range is on another worksheet, so could you help me out with the syntax for that as well. And, since I can't post questions from work, is it possible to select a range and assign it to a variable like [Source] and [Target] and put those into the filter code. Range("C6").Select Range(Selection, Selection.End(xlDown)).Select Range("C6:C14").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "E6"), Unique:=True As always, any help would be greatly appreciated -- Michael Conroy Stamford, CT . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set of numbers ranging from 0 to 240 | Excel Programming | |||
ranging the filter cells when doing auto filter | Excel Programming | |||
Ranging foruma in excel | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |