Solved How to have a new row automatically appear after data input

January 11, 2015 at 10:52:03
Specs: Macintosh
I'd like to have a macro where every time cels A2:F2 have data inputted into them it automatically creates a new, empty row 2 and shifts all of the data down 1 row. Problem is, I have no Idea how to do that and would greatly appreciate your help. Thanks!

See More: How to have a new row automatically appear after data input

Report •


#1
January 12, 2015 at 07:02:29
✔ Best Answer
Right click the sheet tab for the sheet in which you want this to happen and choose View Code. Paste this into the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made within A2:F2
  If Not Intersect(Target, Range("A2:F2")) Is Nothing Then
'If yes, check to see if all 6 cells contain data
    If WorksheetFunction.CountA(Range("A2:F2")) = 6 Then
'If yes, disable Events, insert Row 2, enable Events
      Application.EnableEvents = False
       Rows(2).Insert shift:=xlDown
      Application.EnableEvents = True
    End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question