CODEWITHSUNDEEP
exceldelete-rowshiftvba
Jill448
Jill448

Reputation: 1793

Delete cells without shifting them up

I have vba macro to delete a selection of cells. Its shifting the cells up automatically after deleting them. I don't want the cells to be shifted up, as I have a chart below the cells and everything gets disturbed if they are shifted.

Here is the code I am using to delete cells

ActiveSheet.UsedRange.Select
ActiveSheet.Range("C2:H8").Select
Selection.Delete

Let me know how can I delete them without shifting up.

Upvotes: 4

Views: 19581

Answers (4)

Tomasz
Tomasz

Reputation: 11

In my case it was easy to use Delete without disturbing row sequence simply starting the loop from the very bottom line towards the header of the selected scope:

Cells(rw, 4).Activate
For i = rw To 1 Step -1
   Set Clr = Cells(i, 4)
   If Not Clr.Text Like "R*" Then
    Clr.EntireRow.Delete Shift:=xlUp

   End If
Next i

Upvotes: 1

Steve
Steve

Reputation: 1648

Have you considered just ClearContents?

ActiveSheet.Range("C2:H8").ClearContents

If you have problems with merged cells try:

ActiveSheet.Range("C2:H8").Value = ""

Upvotes: 1

P. O.
P. O.

Reputation: 195

You could use 

ActiveSheet.Range("C2:H8").ClearContents

that'll empty the content without shifting anything.

Upvotes: 5

adpda
adpda

Reputation: 63

If i understand correctly, I think using ClearContents instead of Delete should provide the functionality you are looking for.

http://msdn.microsoft.com/en-us/library/office/aa223828%28v=office.11%29.aspx

Alternatively, you can specify how you want the cells to shift, if that is better suited to what you are trying to do:

From: http://msdn.microsoft.com/en-us/library/office/aa223863%28v=office.11%29.aspx

expression.Delete(Shift)

expression Required. An expression that returns a Range object.

Shift Optional Variant. Used only with Range objects. Specifies how to shift cells to replace deleted cells. Can be one of the following XlDeleteShiftDirection constants: xlShiftToLeft or xlShiftUp. If this argument is omitted, Microsoft Excel decides based on the shape of the range.

Upvotes: 3

Related Questions