Auto insert a blank row above the current row based on a cell val.
 

I would like to automatically insert a blank row in a spreadsheet everytime
the cell value in a particular column changes. The blank row should appear
above the row with the new value.

Auto insert a blank row above the current row based on a cell val.
 

If you want to test for change in Col A then
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 2) = "$A" Then ' replace A with your column
Target.EntireRow.Insert
End If
End Sub


"mattwill" wrote:

I would like to automatically insert a blank row in a spreadsheet everytime
the cell value in a particular column changes. The blank row should appear
above the row with the new value.

Auto insert a blank row above the current row based on a cell val.
 

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

The particular column in this code would be column A(1)

Edit to suit


Gord Dibben MS Excel MVP


On Thu, 6 Nov 2008 11:05:01 -0800, mattwill
wrote:

I would like to automatically insert a blank row in a spreadsheet everytime
the cell value in a particular column changes. The blank row should appear
above the row with the new value.