excel vba - filters and ranges

  • Thread starter Thread starter dbuchana
  • Start date Start date
D

dbuchana

I have a list of transactions to which I add records daily. I want to
to filter and extract records to another location on the spreadsheet
based on user defined criteria. My problem is that the code initially
selects the correct range but range references for the filter do not
appear to change as records are not does the range for filter critieria
adjust to reflect the addition of records. Obviously I am new to VBA
but help would be appreciated.

File format
Date CP Notional Cur Type Rate
28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
28-Jul-06 BNS 1,500,000 USD FWD 1.1250


Filter Criteria
Date CP
28-Jul-06 RBC


Code
Sub Macro1()
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
unique:=False
End Sub
 
So the issue is that you are inserting rows and moving your criteria range
every time you insert rows? I assume you are also having an issue in that the
paste area needs to be incremented as you insert rows? It is a little hard to
tell from your post...
 
Yes. As I add another row the criteria range moves down another row but
macro does update the change . In additon the range used for the
filter....("A1:I4").AdvancedFilter does not capture the additonal row.
The paste area will also have to change.
 
Give this code a try. You will need to add a named range for the criteria
range. select the entire Criteria Range (A7:C8) and In the drop down just
above Column A where it says A7 place your cursor and overwrite it to the
word Criteria. This code should then work for you... (Extract is a named
range created automatically for you on an advanced filter copy, so if you
select A10 you will see the word exctract in the cell reference aboce column
A)...

Sub Macro1()
Dim rngToFilter As Range

Set rngToFilter = Range(Range("A1"),
Range("A1").End(xlToRight).End(xlDown))
rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End Sub

P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
too... I am off to a meeting for a while. I will check back a little later to
see how things are going for you...
 
Thanks again. When i paste the code I get an error highlighted in red
for the following portion. Unfortunately, I am not able to resilve the
issue due to my inexperience

Set rngToFilter = Range(Range("A1"),
Range("A1").End(xlToRight).End(xlDown))
 
Sorry the Window wrapped the text... It should all be on one line in your
code window... or use this...

Sub Macro1()
Dim rngToFilter As Range

Set rngToFilter = Range(Range("A1"), _
Range("A1").End(xlToRight).End(xlDown))
rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End Sub

PS... My sister is Michelle Nutting... you would think I would know her
married name...
 
I have not met your sis - i am in Treasury but i will drop by. I named
the range but the code results in a "400 error" or a run time error
"1004 with application defined or object defined error" if I step
through it. Too bad you cannot attach files in the forum.
 
I just e-mailed you a spreadsheet showing you what I have... It could be an
issue with the named ranges most likely... Let me know if you don't get it or
if you have any problems with it...
 
Back
Top