Sorry for the repeated post, I thought the title was a little ambiguous and didn't know how to edit it.
Hi, I've got a vba code that automatically fills formulae down a range of cells when a row is inserted. This is all working well, but I would just like to add the same sort of function for when the row is deleted as well.
Can anyone help?
[face=Courier New]
Private Sub Worksheet_Calculate()
Dim lr As Long
ActiveSheet.Unprotect Password:="Bungle" ' Unprotect sheet to allow vba code to run
Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Range("Q1").Value ' This is a reference cell for determing the range to autofill
If lr > Range("Q2").Value Then
Range("K7:M" & lr).FillDown ' Fills down formulae from K7, L7 and M7
Range("K7:M" & lr).Locked = False ' This is to unlock the cells in the range so user can edit them
End If
Range("Q2").Value = lr
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.protect Password:="Bungle", _
DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True ' Re-protect sheet and allow deleting and inserting of cells
End Sub
[/face]
Hi, I've got a vba code that automatically fills formulae down a range of cells when a row is inserted. This is all working well, but I would just like to add the same sort of function for when the row is deleted as well.
Can anyone help?
[face=Courier New]
Private Sub Worksheet_Calculate()
Dim lr As Long
ActiveSheet.Unprotect Password:="Bungle" ' Unprotect sheet to allow vba code to run
Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Range("Q1").Value ' This is a reference cell for determing the range to autofill
If lr > Range("Q2").Value Then
Range("K7:M" & lr).FillDown ' Fills down formulae from K7, L7 and M7
Range("K7:M" & lr).Locked = False ' This is to unlock the cells in the range so user can edit them
End If
Range("Q2").Value = lr
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.protect Password:="Bungle", _
DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True ' Re-protect sheet and allow deleting and inserting of cells
End Sub
[/face]