ExcelBanter - Function to insert rows on a change in a cell

See Chip Pearson's site for more on event macros.

http://www.cpearson.com/excel/Events.aspx

This would be an example of event code....................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim num As Integer
num = 1
On Error GoTo stoppit
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
Target.Offset(1, 0).Resize(num).EntireRow.Insert
stoppit:
End Sub

When you manually change the data in A1, you will insert a blank row below that.

Right-click on the worksheet tab and "View Code" Copy/paste into that sheet
module.

Alt + q to return to the Excel worksheet window.

BUT...................if your question was to insert a row at every change in
value down a column.

i.e. column A has values of

abc
abc
abc
def
def
def
ghi
ghi
ghi

You want to insert a row at each change you would use a regular macro.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Tue, 18 Mar 2008 18:34:00 -0700, Subhash
wrote:

Thanks Pete

What is an event macro ?

"Pete_UK" wrote:

Not using a function or a formula. You can only do that with an event macro.

Pete

"Subhash" wrote in message
...
Is it possible to automatically insert a blank row after a change in a
cell
value