Deleting specific cells in a selected row

  • Thread starter Thread starter Jeff Wright
  • Start date Start date
J

Jeff Wright

I want to write a subroutine which will delete specific cells in a row. For
example, if the active cell is A13, I want the macro to delete cells B13
through E13, and also cells H13 through M13.

Your help is appreciated.

Jeff
 
Do you want to delete the cells, or just clear them?


Public Sub DeleteCells()
With ActiveCell
Union(.Offset(0, 1).Resize(1, 4), _
.Offset(0, 7).Resize(1, 6)).Delete Shift:=xlToLeft
End With
End Sub


Public Sub ClearCells()
With ActiveCell
Union(.Offset(0, 1).Resize(1, 4), _
.Offset(0, 7).Resize(1, 6)).ClearContents
End With
End Sub
 
Yes, "clear" is what I want (I'll remember to use correct terminology next
time). This works just fine!

Thanks for your help!!

Jeff
 
One more question: In order for the "clear contents" version of your macro
to work in my specific application, I need a routine which places the active
cell in column A. Otherwise, the macro will clear the incorrect cells. (The
purpose of my macro is to allow the user to delete entries made in any given
row, without [of course] deleting those cells containing formulas.) Is
there a line of code I could insert which would place the active cell in
column A?

Thanks for your help!

Jeff
 
One way:

Public Sub ClearCells()
With ActiveCell
Union(Cells(.Row, 2).Resize(1, 4), _
Cells(.Row, 8).Resize(1, 6)).ClearContents
End With
End Sub

but if you want to clear just the cells containing constants (not
formulae):

Public Sub ClearCells()
On Error Resume Next 'in case no constants
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub
 
Hi, JE

Re your code below which deletes constants, not formulae: this is just
absolutely excellent and exactly what I needed. Thanks so much for your
help.

Jeff



JE McGimpsey said:
One way:

Public Sub ClearCells()
With ActiveCell
Union(Cells(.Row, 2).Resize(1, 4), _
Cells(.Row, 8).Resize(1, 6)).ClearContents
End With
End Sub

but if you want to clear just the cells containing constants (not
formulae):

Public Sub ClearCells()
On Error Resume Next 'in case no constants
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub


Jeff Wright said:
One more question: In order for the "clear contents" version of your
macro
to work in my specific application, I need a routine which places the
active
cell in column A. Otherwise, the macro will clear the incorrect cells.
(The
purpose of my macro is to allow the user to delete entries made in any
given
row, without [of course] deleting those cells containing formulas.) Is
there a line of code I could insert which would place the active cell in
column A?
Click to expand...
Click to expand...
 
Back
Top