VBA: Delete name range
 

I want to delete the named range that is created while updating my web query.
Can someone help with the delete code, below is what I've tried and it
doesn't delete. The first arrow is what the range is named, the second is to
delete the named range although it doesn't work.


Sub Macro14()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=" & Range("B3").Value,
Destination:=Range("J5"))
---- .Name = "Macro" & Range("B3").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "42,45,48"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
---- ActiveWorkbook.Names("Macro" & Range("B3").Value).Delete
End Sub

VBA: Delete name range
 

This might work

ActiveSheet.Names("Macro" & Range("B3").Value).Delete

"CM4@FL" wrote:

I want to delete the named range that is created while updating my web query.
Can someone help with the delete code, below is what I've tried and it
doesn't delete. The first arrow is what the range is named, the second is to
delete the named range although it doesn't work.


Sub Macro14()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=" & Range("B3").Value,
Destination:=Range("J5"))
---- .Name = "Macro" & Range("B3").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "42,45,48"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
---- ActiveWorkbook.Names("Macro" & Range("B3").Value).Delete
End Sub

Delete name range
 

For Each n In ActiveSheet.Names
If InStr(c, "Macro") Then n.Delete
Next
But better to establish the query and just refresh using your range instead
of new one each time.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CM4@FL" wrote in message
...
I want to delete the named range that is created while updating my web
query.
Can someone help with the delete code, below is what I've tried and it
doesn't delete. The first arrow is what the range is named, the second is
to
delete the named range although it doesn't work.


Sub Macro14()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=" & Range("B3").Value,
Destination:=Range("J5"))
---- .Name = "Macro" & Range("B3").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "42,45,48"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
---- ActiveWorkbook.Names("Macro" & Range("B3").Value).Delete
End Sub

Delete name range
 

Oops

For Each n In ActiveSheet.Names
If InStr(n.Name, "Macro") Then n.Delete
Next n
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
For Each n In ActiveSheet.Names
If InStr(c, "Macro") Then n.Delete
Next
But better to establish the query and just refresh using your range
instead of new one each time.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CM4@FL" wrote in message
...
I want to delete the named range that is created while updating my web
query.
Can someone help with the delete code, below is what I've tried and it
doesn't delete. The first arrow is what the range is named, the second is
to
delete the named range although it doesn't work.


Sub Macro14()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=" & Range("B3").Value,
Destination:=Range("J5"))
---- .Name = "Macro" & Range("B3").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "42,45,48"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
---- ActiveWorkbook.Names("Macro" & Range("B3").Value).Delete
End Sub