Advanced filter named range

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,502
Office Version
  1. 2019
Platform
  1. Windows
Am I correct in saying using an Advanced Filter in VBA automatically creates a named range called Extract?

I have the following:

Code:
MyRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CriteriaRng, CopyToRange:=OutputRng

and after running it, I see a named range.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,443
Office Version
  1. 365
Platform
  1. Windows
If you copy to a different range then you will get a Defined Name "Extract" regardless of how you run the advanced filter.
 
Solution

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,502
Office Version
  1. 2019
Platform
  1. Windows
If you copy to a different range then you will get a Defined Name "Extract" regardless of how you run the advanced filter.
Thanks

I can see it being dangerous if you already had a named range called Extract.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,443
Office Version
  1. 365
Platform
  1. Windows
Don't use Extract or Criteria for named ranges if you are going to use advanced filter.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,502
Office Version
  1. 2019
Platform
  1. Windows
Don't use Extract or Criteria for named ranges if you are going to use advanced filter.
My usual preference when releasing tools into production is to hide all named ranges (because I feel it's "cleaner"), so I guess going forwards, I should stop doing that!
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top