New Named Range Created Each Time Data Imported into Excel via Macro

C

Carroll Rinehart

I have noticed that each time I import data into an Excel spreadsheet
via a macro, a new named range (for the same range) is created. This
does not pose a problem, but after a while, I'll have a huge number of
named ranges that will never be used. Why does Excel name the range
and how can I stop this? I noticed in the recorded macro, there was a
line .Name = "drd_5". I commented this out to see what would happen,
but it just renamed the range "ExternalData_5". The next one was
"ExternalData_6", etc.

Thanks,
Carroll Rinehart
 
D

Don Guillett

You could refresh with different parameters or add this

For Each Name In Sheets("Data").Names
Name.Delete
Next Name

or even qualify it if you don't want to delete all
if left(name,3)="Ext" then
 
Top