Add the current selection to the Edit Range with VBA

[Lade]

Hello,

I have a protected worksheet that has a certain range of cells set as an Edit Range. I wrote a macro to be able to insert a new line in that edit range, but I can't figure out how to update the Edit Range to include the inserted line. After some research it sounds like maybe the edit range can't be updated but just has to be deleted and then another made with the total range. Is that correct?

Dim Tstamp As Date
Tstamp = Now()

ActiveSheet.Protection.AllowEditRanges.Add _
Title:=Tstamp, _
Range:=Selection   'Selection is the new row that the macro just added

One thing I tried was naming the new ranges by the date/time that the macro was run, that way each one will be unique, but I couldn't figure out how to set the title of the range with the timestamp. Am I missing something in here?

Basically, I want to run the insert line macro as many times as I need and have the edit range expand accordingly.

 

[John_w]

When you insert a row in a Range object, the Range object expands automatically to include the new row:

Public Sub Expand_Range()

    Dim r As Range
   
    Set r = Worksheets("Sheet2").Range("A1:D4")
    r.Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Debug.Print r.Address 'now A1:D5
   
End Sub

The same thing happens with an AllowEditRange object - you don't have to delete the existing object and add a new one with the new row included. This macro shows how to get an existing Allow Edit Range (the first one on Sheet1), unprotect the sheet and insert a new row in the Allow Edit Range:

Public Sub Expand_Edit_Range()

    Dim ws As Worksheet
    Dim er As AllowEditRange
   
    Set ws = Worksheets("Sheet1")
    Set er = ws.Protection.AllowEditRanges(1)

    MsgBox "Allow Edit Range before expanding: " & er.Range.Address
   
    ws.Unprotect
    er.Range.Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
    MsgBox "Allow Edit Range after expanding: " & er.Range.Address
   
End Sub

 

[Lade]

That makes sense.
Maybe why it wouldn't work for me was I was running the macro on the first row and it wasn't expanding the edit range because it was copying the new row from the row above which is not in the edit range? Maybe I need to adjust the range to include the header row.