4

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
Community
  • 1
  • 1
y-i_guy
  • 673
  • 6
  • 8
  • Your code will work in a new file but when you re-run the code, it will create named ranges like "history_1", "history_2" etc. The surprising part is even if you delete the named range from the named manager, the above behavior can be seen. :) – Siddharth Rout Aug 18 '14 at 17:31
  • You are making an accurate statement. I did discover that I had to reopen the workbook since it was creating a new name (history_1, _2, etc) every time the import code ran. However, that wasn't triggering the Application-defined or Object-defined error I kept encountering. – y-i_guy Aug 19 '14 at 22:31

1 Answers1

5

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

y-i_guy
  • 673
  • 6
  • 8