Run time error 1004
I am attempting to delete a range of cells in an Excel spreadsheet using a Macro. The range is one row by 8 columns. However, when the line of VBA code actually doing the deletion is reached I get a "Run-time error '1004': Delete method of Range class failed."
The actual line causing the error is:
Selection.Delete Shift:=x1Up
where I have previously selected the range. All of the cells in the range do have data and I have security set to the lowest level.
The line causing the error was obtained by recording a macro on another sheet and this was the code generated.
I also tried another variation, which was
Range(strSort).Delete Shift:=x1Up
but got the same error.
The section of code containing this problem line is as follows:
Sheets("tune").Select
strSort = "A" & CStr(intCounter) & ":H" & CStr(intCounter)
Range(strSort).Select
Selection.Delete Shift:=x1Up
Any suggestion as to how I can get around this error or what is causing it?
I posted a similar thread recently. There are a few helpful references provided by Brenda in her reply. I found that to cure the problems I had, I need to fully qualify the reference to the ranges.
In the end I created an Excel,sheet variable to hold the reference to the page I wanted to work with. In theory this was not necessary, in practice it was! I mainly work with Office 97 (my clients choice!).
Hope this helps
ADG at 2007-10-2 >
Hi All,
First off, my computer knowledge is very limited so forgive me if I sound redudant. I'm having the exact same problem when I tried to import an excel file into Access, the following message appears: Run-time error '1004', Delete method of Range Class failed. I've been reading all of these threads and it just makes no sense to me as to how I would fix this problem. I was told that the security level on my excel spreadsheet needs to be on low, which it already is, and I still get the exact same error. Please help!!! (the error is highlighted in yellow).
'Deleting excess rows due to sub totaling of status groups
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Type Total"
Range("A65536").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
If ActiveCell.Row = 1 Then
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.EntireRow.Select
End If
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="="
Range("D65536").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
If ActiveCell.Row = 1 Then
Range("D65536").Select
Selection.End(xlUp).Select
ActiveCell.EntireRow.Select
End If
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
Selection.AutoFilter
Rows("1:1").Delete
'Deleting remaining rows
Columns("J:L").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
'Done
Range("A1").Select
'// Deletes the macro from the workbook
Dim xDelMacro As Object
Set xDelMacro = Application.VBE.ActiveVBProject.VBComponents
xDelMacro.Remove VBComponent:=xDelMacro.Item("DMOS_Export_Prep")
End Sub
I found the error was caused by local range references. You need to replace
Cells(...) --> Worksheets(?).Cells(...) ' replace '?' and '...' yourself.
Range(...) --> worksheets(?).Range(...)
etc
For the following code, I also got Run-Time error 1004. What is the problem with this code?
Sub CommandButton1_Click()
'===============================================
'Rand - Return a random number in a given range.
'
'Parameters:
' Low - The lower bounds of the range.
' High - The upper bounds of the range.
'
'Returns:
' Returns a random number from Low..High.
'===============================================
'Public Function Rand(ByVal Low As Long, _
ByVal High As Long) As Long
' Rand = Int((High - Low + 1) * Rnd) + Low
'End Function
Dim Col As Long
Dim Row As Long
For Col = 0 To 100
For Row = 0 To 100
ThisWorkbook.Sheets("Random Function").Cells(Col, Row + 3) = Int((2 - (-2) + 1) * Rnd) + (-2)
Next Row
Next Col
End Sub
The variable Col will have a value of zero, which will cause an error.
Also you appear to be swapping row and col. The Cells object takes a Row then a Column argument.
ThisWorkbook.Sheets("Random Function").Cells(Row + 3 , Col) =
Because Cells(1,1) is equivalent to range("A1")
Therefore Cells(0,1) would be 1 row above, which does not exist.
