Trying to delete a range with VBA - not working

[rizzo93]

I need to delete a range and I want to preserve the cells around it, so deleting all of the rows is not an option at this point.


So far, my research has shown that you can only delete an entire row. You can shift the cells up or to the left, but I have found nothing that shows how you can delete a range of cells without deleting the entire rows.


When I step through the code, each time I come to the line highlighted in red below, I get the following message: Run-time error '1004': Application-defined or object-defined error".


Is there a way to do this?

Sub PrepPasteWorksheets()
Worksheets("Processed Resources").Activate
    Dim r As Integer
    r = Worksheets("Processed Resources").Range("B8").Value
    daRange = "E9:BN" & CStr(r)
    Worksheets("Processed Resources").Range(daRange).Select[COLOR=#ff0000]
    Range(daRange).Delete Shift:=xlToUp[/COLOR]
Worksheets("Project Rep Data").Activate
    Dim rr As Integer
    rr = Worksheets("Project Rep Data").Range("B8").Value
    daRange = "E8:E" & CStr(rr - 1)
    Worksheets("Project Rep Data").Range(daRange).Select
    Selection.Copy
Worksheets("Processed Resources").Range("E8").ActiveCell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[njimack]

I think it should be

Range(daRange).Delete [B]xlUp[/B]

 

[rizzo93]

I think it should be

Range(daRange).Delete [B]xlUp[/B]

Hi njimack and thanks for the assist.


I tried changing that portion of the code, and it did get rid of the error I was getting.