MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automatic Row creation

Posted by Rick D. on January 04, 2002 11:02 AM

Hi All,
I am trying to make an analysis we do at work more efficient. I have used many suggestions found at this web site and message board. I need help with one last thing. I would like to create a macro that when one enters data into cell A1, A2, etc. that the macro automatically creates the next row with the same format, sizes, and formulas as the previous row. Is this possible. Does anyone have any suggestions.

Posted by Yorick on January 04, 2002 2:58 PM


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Offset(1, 0) = "" Then
With Target.EntireRow
.Copy .Offset(1, 0)
.Offset(1, 0).SpecialCells(xlCellTypeConstants, 23).ClearContents
End With
End If
Application.EnableEvents = True
End Sub

Posted by Rick D. on January 07, 2002 12:34 PM

Thanks Yorick, I will try it out.