VBA code with autofill when inserting new rows within a specific column range

[valmir]

Hello everyone!
I have this code which is a generic code that is actually working. However, it affects the entire row. I need this code modified so that it will make changes from column A to DO.
This is the code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

Please help!

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   With Range("A" & Target.Row).Resize(, 119)
      .Offset(1).Insert xlShiftDown
      .Copy .Offset(1)
      On Error Resume Next
      .Offset(1).SpecialCells(xlConstants).ClearContents
      On Error GoTo 0
   End With
End Sub

 

[valmir]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True With Range("A" & Target.Row).Resize(, 119) .Offset(1).Insert xlShiftDown .Copy .Offset(1) On Error Resume Next .Offset(1).SpecialCells(xlConstants).ClearContents On Error GoTo 0 End With End Sub

It works perfectly! Thank you for saving my day! ???

 

[Fluff]

You're welcome & thanks for the feedback.

 

[valmir]

You're welcome & thanks for the feedback.

Hi Fluff
Sorry to bother you again
I forgot one small detail which is adding a row limit as well.
So, instead of just column DO, could you please make it so as to make changes up to row 42 as well (in addition to column DO)?

 

[Fluff]

Do you mean that it could only work on certain rows?

 

[valmir]

Yes! from row 5 up to row 42 and from column A up to column DO, so in this case the range would be from A5 to DO42

 

[Fluff]

Ok, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("A5:DO42")) Is Nothing Then
      Cancel = True
      With Range("A" & Target.Row).Resize(, 119)
         .Offset(1).Insert xlShiftDown
         .Copy .Offset(1)
         On Error Resume Next
         .Offset(1).SpecialCells(xlConstants).ClearContents
         On Error GoTo 0
      End With
   End If
End Sub

 

[valmir]

Ok, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("A5:DO42")) Is Nothing Then
      Cancel = True
      With Range("A" & Target.Row).Resize(, 119)
         .Offset(1).Insert xlShiftDown
         .Copy .Offset(1)
         On Error Resume Next
         .Offset(1).SpecialCells(xlConstants).ClearContents
         On Error GoTo 0
      End With
   End If
End Sub

This one is having the same behaviour as the previous one. I mean, it works fine for the columns part (there are no changes beyond column DO) but it doesn't work for the rows. I double-clicked to add a new row (for example I double-clicked on row 12 and it added a new row below but all the rows below 12 shifted one row down. It was supposed to shift only up to row 42 but it didn't happen

 

[Fluff]

If you insert a row, then everything below that row will shift down.