Donald Wm. Johnson


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





Re: Run time error 1004

ADG


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






Re: Run time error 1004

MS ISV Buddy Team

ADG is correct... you can view this thread at http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=337016&SiteID=1

-brenda (ISV Buddy Team)







Re: Run time error 1004

jrmoto

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






Re: Run time error 1004

Andy Pope

At a guess I would think Autofilter is still on when you try and delete the selection.
I not really sure what your code is trying to do but maybe add a line to clear any filtering before deletion.





Re: Run time error 1004

LJry

I found the error was caused by local range references. You need to replace

Cells(...) --> Worksheets( ).Cells(...) ' replace ' ' and '...' yourself.

Range(...) --> worksheets( ).Range(...)

etc





Re: Run time error 1004

sunzhoujian

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





Re: Run time error 1004

Andy Pope

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) =





Re: Run time error 1004

sunzhoujian

Thanks a lot Andy!

It works with your suggestion. I changed them both from 0 to 1.

However, why couldn't Col be zero





Re: Run time error 1004

Andy Pope

Because Cells(1,1) is equivalent to range("A1")

Therefore Cells(0,1) would be 1 row above, which does not exist.





Re: Run time error 1004

thinhbui

Hey guys.

I have ran into a wall with this portion of the VBA code. This macro was orignally written for 2003 excel, however, since the 2007 increased it's pivot table limits, I wanted to take full advantage of this.

All I did was increase the range and everything else should be the same, however, I got a run time error 1004 telling me that

"A field in your source data has more unique items than can be used in a PivotTable report. Microsoft Office Excel may not be able to create the report....."

here is the code snippit.

If CurrRow > 250000 + 18 Then
IntPress = MsgBox("Too many unique records for Pivot table; only 250000 allowed")
Exit Sub
End If
Set BaseRange = ActiveWorkbook.Worksheets(SheetNeeded).Range("a18:i150000")
BaseString = "'[" & BaseBook.Name & "]" & BaseRange.Worksheet.Name & "'!" & BaseRange.AddressLocal(ReferenceStyle:=xlR1C1)

Windows(CurrBook).Activate
#If xlver > 10 Then
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array(Array(BaseString, "Basecase") _
, Array(CompString, "ChangedCase"))). _
CreatePivotTable TableDestination:="", TableName:="PivotTable3"

#Else
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array(Array(BaseString, "Basecase") _
, Array(CompString, "ChangedCase"))). _
CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10
#End If

The problem is where the #Else statement starts. That is the only area that is given me that error 1004.

Any help would be appreciate it

PS. I would assume that if I change the range it should accomondate for the increase of pivot items in 2007 excel.





Re: Run time error 1004

Phillb

I also have a problem with the same run-time error. My macro copy and pastes information. It works fine when run as a macro, but when I assign it to a comand button I get the run-time error 1004.