prevent row deletion

[annadinesh]

Dear Experts

Please provide a code for Disable/Restrict Row Delete for top 4 Rows only, after row 5 we can delete the rows,


Regards


Dinesh Saha

 

[Legacy 143009]

Hello!
Paste this code into sheet you want to prevent deletion:

Public RowCnt As Long
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range(Rows(1), Rows(4))) Is Nothing And RowCnt <> ActiveSheet.UsedRange.Rows.Count Then
    With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
    End With
  Else
    Exit Sub
  End If
End Sub

Paste this code into Workbook:

Private Sub Workbook_Open()
  Worksheets("Sheet1").RowCnt = ActiveSheet.UsedRange.Rows.Count 'change Sheet1 with your actual sheet name.
End Sub

 

[HaHoBe]

Hi Flashbond,

AFAIK Public Variables must be defined in standard modules - trying to use your code RowCnt shows no value.

Holger

 

[Legacy 143009]

Yes I tought the same. Also RowCnt in open workbook event is empty. But RowCnt shows a value if you break at this line. Weird..

  If Not Intersect(Target, Range(Rows(1), Rows(4))) Is Nothing And RowCnt <> ActiveSheet.UsedRange.Rows.Count Then

 

[Legacy 143009]

@HaHoBe I added that check for not to alarm on column deletes. Do you have another trick to bypass column deletions?

 

[HaHoBe]

Hi Flashbond,

I would add a hidden name for a range once and refer to this name inside Worksheet_Change.

Adding the Name may look like this (change sheetname and range name/area to suit but make sure to refer to the proper Name in Worksheet_Change):

Sub SetHiddenName()

  Dim oName           As Name
 
  Const cstrRgName    As String = "KeepRows"
  Const cstrRgRange   As String = "TestForKeepHeaderRows!A1:ZZ4"

  For Each oName In ActiveWorkbook.Names
    If oName.Name = cstrRgName Then
      oName.Delete
      Exit For
    End If
  Next oName

  ActiveWorkbook.Names.Add Name:=cstrRgName, _
                           RefersTo:="=" & cstrRgRange, _
                           Visible:=False

End Sub

Code behind the sheet (in this sample TestForKeepHeaderRows):

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("KeepRows")) Is Nothing Then
    With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
    End With
  Else
    Exit Sub
  End If
End Sub

You may run SetHiddenName again if you want to refer to a different area (more or less rows for the header) after changing the constant cstrRgRange.

Ciao,
Holger