Delete method of Range class failed (again)

[Kelden]

When I run the following code, I get a Run-time Error '1004': Delete method of Range class failed. If there is nothing in column D, then the if statement is not satisfied and the macro runs fine and doesn't do anything.
Changing the Delete line to 'Rows(i).Delete Shift=x1Up' works just fine, but I would prefer to just delete the selected range.
Here is the entire macro:

Sub AssignRequests()
    Dim i As Integer
    i = 2
    Do While i <= Range("OutstandingCount") + 10
    If Range("D" & i) <> "" Then
    Range("PasteRange").Value = Range("A" & i, "D" & i).Value
    
       Range("A" & i, "D" & i).Delete Shift:=x1Up
    
    Else: i = i + 1
    End If
    Loop
End Sub

This is my first post, so I hope it is done correctly.
Thanks!

[Mordred]

Hi Kelden, you have to delete the entire row. Replace

Range("A" & i, "D" & i).Delete Shift:=x1Up

with

Range("A" & i, "D" & i).EntireRow.Delete Shift:=x1Up

[jeffreybrown]

Hi Kelden,

You might also want to take a closer look at

Shift:=x1Up

That should be a xlUp not a 1

[romperstomper]

It's not X1UP but XLUP.

[romperstomper]

Mordred,
You do not have to delete entire rows, you can delete cells just as you do in the UI.

[Mordred]

Good eyes guys! I didn't even notice that, I just typed in what I did and it worked, even with the x1Up. Quirky.

@romperstomper, how then would you know when to use EntireRow or not?

[romperstomper]

If you delete the entire row, there's no way to go but up, so it gets ignored.

It's up to you whether you want the whole row deleted or not. If you just want to take a few cells out of a table, you may just want to delete the cells so as not to disturb other columns on the sheet.

[Mordred]

In that case, wouldn't

.ClearContents

be more appropriate? I feel like I am spamming this thread!

[romperstomper]

Only if you want empty cells in the middle of your table. If you delete the cells, then the cells below (or to the right of) them in the same columns (or rows) shift up (or left) to fill in the spaces. If you clearcontents, then nothing moves.

[Kelden]

Hi guys,
Thanks for the quick replies! 'Shift:=xlUp' worked as I would like it to. Your help is much appreciated!!

[stephenkraj]

This Error occurs if your sheet is protected. Unprotect and delete it.