VBA - Insert row, copy contents of original row except for contents of columns A-N

R

Royzer

Hi. This code inserts a row and copies formulas (only) to the new row
The problem I have run into is that I didn't know our users were addin
and subtracting within cells, which means their numbers are bein
duplicated on the new row when those new cells are supposed to be blank
I need to find a way to make cells on the new row blank from column
through column N.

Thanks!


Code
-------------------

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Target
.Offset(1).EntireRow.Insert
.EntireRow.Copy .Offset(1).EntireRow(1)
With .Offset(1).EntireRow
.Cells(1).ClearContents
On Error Resume Next
.SpecialCells(2).ClearContents
On Error GoTo 0
End With
End With
End Sub
 
D

Don Guillett

Hi. This code inserts a row and copies formulas (only) to the new row.
The problem I have run into is that I didn't know our users were adding
and subtracting within cells, which means their numbers are being
duplicated on the new row when those new cells are supposed to be blank.
I need to find a way to make cells on the new row blank from column A
through column N.

Thanks!

Code:
--------------------

  Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByValTarget As Range, Cancel As Boolean)
  Cancel = True
  With Target
  .Offset(1).EntireRow.Insert
  .EntireRow.Copy .Offset(1).EntireRow(1)
  With .Offset(1).EntireRow
  .Cells(1).ClearContents
  On Error Resume Next
  .SpecialCells(2).ClearContents
  On Error GoTo 0
  End With
  End With
  End Sub
Earlier post had a slightly different request. "clear for b>>>>"
Just change the code I sent earlier from 256 to 13. Put in
ThisWorkbook module
Fires from col A

Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
With Target
Rows(.Row + 1).Insert
Rows(.Row).Copy .Offset(1)
Cells(.Row + 1, 2).Resize(, 14).ClearContents
..Offset(1).Select
End With
Application.ScreenUpdating = True
End Sub
 
L

Litol_Szeth

Hi,

try using this example

Sheets("Sheet Name").Select
Range("cell number/s").Select
Selection.ClearContent
 
R

Royzer

Litol_Szeth;1453333 said:
Hi,

try using this example

Sheets("Sheet Name").Select
Range("cell number/s").Select
Selection.ClearContents

Thank you, Litol_Szeth. I had already found this code before I saw you
post:

Code
-------------------

'Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Target
.Offset(1).EntireRow.Insert
.EntireRow.Copy .Offset(1).EntireRow(1)
'With .Offset(1).EntireRow
.Cells(1).Resize(, 14).ClearContents
On Error Resume Next
.SpecialCells(2).ClearContents
On Error GoTo 0
End With
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top