DaveGib
02-05-2014, 10:05 AM
Hi All,
I have some code that searches through a data table and puts the result in col AA. this data can, and does, contain duplicates, so with vba, I apply an Advanced filter to give a unique list and paste in col AB.
This all works well the first time, but if I rerun my query I get an error, and discovered that the advanced filter puts a named range called "Extract" in AB1.
I have some code to .clear cols AA:AB before re-running, it clears the data but not the named range
If I manually delete the named range the code then runs ok again - once!
I cant seem to find a way to delete the named range, without creating a function, which I know nothing about.
Has anyone any suggestions??
The code below is what I use for the advanced filter, - would there perhaps be another way to get the same result - I need to get a unique, sorted list in col AB from column AA - without the advanced filter.... or to be able to just delete the range
Any help or suggestions would be appreciated
Dave
If numSubbieHouses > 1 Then ' Check if there is more than 1 house to apply adv filter ....
Range("AA2:AA" & numSubbieHouses & "").Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AB1"), Unique:=True ' remove any duplicates from col AA
Else: Range("AA1").Select ' ... if only 1 house, just copy and paste the single house number
Selection.Copy
Range("AB1").Select
Selection.Range("AB1").PasteSpecial xlPasteValuesAndNumberFormats
I have some code that searches through a data table and puts the result in col AA. this data can, and does, contain duplicates, so with vba, I apply an Advanced filter to give a unique list and paste in col AB.
This all works well the first time, but if I rerun my query I get an error, and discovered that the advanced filter puts a named range called "Extract" in AB1.
I have some code to .clear cols AA:AB before re-running, it clears the data but not the named range
If I manually delete the named range the code then runs ok again - once!
I cant seem to find a way to delete the named range, without creating a function, which I know nothing about.
Has anyone any suggestions??
The code below is what I use for the advanced filter, - would there perhaps be another way to get the same result - I need to get a unique, sorted list in col AB from column AA - without the advanced filter.... or to be able to just delete the range
Any help or suggestions would be appreciated
Dave
If numSubbieHouses > 1 Then ' Check if there is more than 1 house to apply adv filter ....
Range("AA2:AA" & numSubbieHouses & "").Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AB1"), Unique:=True ' remove any duplicates from col AA
Else: Range("AA1").Select ' ... if only 1 house, just copy and paste the single house number
Selection.Copy
Range("AB1").Select
Selection.Range("AB1").PasteSpecial xlPasteValuesAndNumberFormats