3

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.

Community
  • 1
  • 1
Jill448
  • 1,745
  • 10
  • 37
  • 62
  • When the cells are deleted, the cells from below or from left move to take their place. Regarding your charts, how do they get disturbed? – Siddharth Rout Apr 16 '13 at 20:19

4 Answers4

4

You could use 

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

that'll empty the content without shifting anything.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
P. O.
  • 185
  • 1
  • 14
  • @Dick: I tried clearcontents, But getting an error "Cannot change Merged cells" as I do have few merged cells. – Jill448 Apr 17 '13 at 20:36
  • Ooh, I don't like merged cells. Try `ActiveSheet.Range("C1:H8").MergeArea.ClearContents` – Dick Kusleika Apr 17 '13 at 20:43
  • I've just tried on a mix selection of ordinary and merged cells, and it works perfectly fine. What version of Excel are you using ? – P. O. Apr 18 '13 at 14:37
3

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.

Community
  • 1
  • 1
adpda
  • 63
  • 4
1

Have you considered just ClearContents?

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

If you have problems with merged cells try:

ActiveSheet.Range("C2:H8").Value = ""
Steve
  • 1,620
  • 2
  • 19
  • 33
  • I tried clearcontents, But getting an error "Cannot change Merged cells" as I do have few merged cells. – Jill448 Apr 17 '13 at 20:44
1

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
Tomasz
  • 11
  • 1