Dynamic Ranging

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
Back
Top