Need to add rows when a number is entered

[asokaw]

Hi
I need to accept a number to cell A1 & insert that number of rows to a set of data already typed
Assume I have already entered data for rows 2 - 20 & when I enter 3 in cell A1 it should insert 3 new blank rows before row 2. Need a simple macro to do this

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen that you want to apply this to, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
        If Target > 0 Then
            Rows("2:" & Target.Value + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    End If
        
End Sub

Then, anytime anyone enter a number in A1, it will automatically insert that many rows, starting at row 2.

 

[asokaw]

Thanks a lot for the answer it works, will you be able to tell me how I could expand it to all the rows in that column, meaning when No 2 is entered in A1 it will insert 2 rows starting from A2 which happens now, I need to allow to enter No's in A2,A3,A4 & so on & the same thing to happen

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If (Target.Count = 1) And (Target.Column = 1) Then
        If Target.Value > 0 Then
            Application.EnableEvents = False
            Rows(Target.Row + 1 & ":" & Target.Row + Target.Value).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Application.EnableEvents = True
        End If
    End If
        
End Sub