CODEWITHSUNDEEP
excelnamed-rangesvba
y-i_guy
y-i_guy

Reputation: 773

Run-time error when deleting a named range

I don't understand why I keep getting a "Run-time error '1004': Application-defined or Object-defined error" message when trying to delete a named range.

Here's the code used to import data from a .csv file and name the range as "history"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\<user name>\Downloads\history.csv", Destination:=Range(destCell))
    .Name = "history"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 3
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(3, 1, 2, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Here's the code used to delete the "history" named range. Note that the line immediately before it functions just fine to find the named range. It just doesn't won't delete the name.

Application.Goto Reference:="history"
ActiveWorkbook.Names("history").Delete

Upvotes: 4

Views: 2922

Answers (1)

y-i_guy
y-i_guy

Reputation: 773

Answer: The problem was that the Workbook was using a worksheet name as part of the Name attribute of the named range. Specifically it was using history!history for the name.

Method of Troubleshooting: I used the following code that had been posted to a similar question at http://www.ozgrid.com/forum/showthread.php?t=49079&page=2

Dim nameRng As Name 
Dim varVal As Variant 
On Error Resume Next 
For Each nameRng In ActiveWorkbook.Names 
    varVal = Range(nameRng.Name).Value 
    nameRng.Delete 
Next

The Locals Window in the VBA Editor revealed that the nameRng.Name for this variable was the string "history!history".

Revised code: I removed the Application.Goto Reference:="history" line since it was essentially a non-functional line of code (similar to a Select action) and was left with this as the code to delete the Name of the Imported range:

ActiveWorkbook.Names("history!history").Delete

Platform: I was using Excel 2013 on Windows 7 Professional

Upvotes: 5

Related Questions