Adding a specific row with any cell location in a VBA

[mholton63]

I'd like to insert a row after row 3 no matter where my highlighted cell is. The VBA below will only work if I have cell 4 picked. Is there a way to accomplish what I'm wanting?


Sub Insert_Rows()
With Selection
If .Areas.Count = 1 Then
.EntireRow.Insert
Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("C:U")).FillDown
End If
End With
End Sub

Kind Regards,
Mark

 

[BSALV]

replace selection with 1 or several cells of row 4, counting areas is no longer needed

Sub Insert_Rows()
     With Range("A4")
          .EntireRow.Insert
          Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("C:U")).FillDown
     End With
End Sub

 

[mholton63]

Thanks, That worked great. Is there any way to protect the sheet and keep the VBA working? I keep getting an error 400.

 

[BSALV]

you have to say one time during a session that the file is open this

Sheets("MySheet").Protect userinterfaceonly:=True

So the best place to do is perhaps in the "Workbook_open" event in ThisWorkbook, but it can be everywhere else also ....

 

[JEC]

Try to protect this way:

Activesheet.protect Userinterfaceonly:=true

 

[Siddharth Rout]

Since your range is fixed, you can also do

Sheet1.Rows(4).Insert

Change Sheet1 to the relevant sheet codename. Also since your row number is fixed, instead of

Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("C:U")).FillDown

you can also do

Intersect(Range("3:4"), Range("C:U")).FillDown

As mentioned by other users, you may use `Userinterfaceonly:=true` or simply protect and unprotect in the code as well. Here is an example

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim MyPass As String
   
    MyPass = "SomePassword"
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
   
    With ws
        .Unprotect MyPass
        If Range("A4").Areas.Count = 1 Then
            .Rows(4).Insert
            Intersect(.Range("3:4"), .Range("C:U")).FillDown
        End If
        .Protect MyPass
    End With
End Sub