Insert new row as cell contents change

[George]

Insert new row as cell contents change. After importing
data I have a spread sheet with a column that contains a
series of alpha numeric characters. At various random
intervals in this column the contents change. EG rows 1 to
4 could contain ABC, then rows 5 to 15 could become 222. I
am looking for a method to insert a blank row
automatically between the rows were the contents change.
Many Thanks
Geo

 

[Nick Hodge]

George

If you are familiar with VBA the code below will do what you want.
Preselect the column of data first

Sub InsertRowAfterValueChange()
Dim myCell As Range
Dim sCurrVal As String
sCurrVal = ActiveCell.Value
For Each myCell In Selection
If myCell.Value <> sCurrVal Then
myCell.EntireRow.Insert
sCurrVal = myCell.Value
End If
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[George]

Nick, Many thanks, it worked perfectly.

-----Original Message-----
George

If you are familiar with VBA the code below will do what you want.
Preselect the column of data first

Sub InsertRowAfterValueChange()
Dim myCell As Range
Dim sCurrVal As String
sCurrVal = ActiveCell.Value
For Each myCell In Selection
If myCell.Value <> sCurrVal Then
myCell.EntireRow.Insert
sCurrVal = myCell.Value
End If
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)





.