Spreadsheet contains a data range from cell A4 that is
refreshed regularly with updated data. Column A contains a
list of cities and is sorted A-Z. I am trying to cretae
macor that will insert a blank row between each change of
city all the way down. Cuurent attempt as follows but does
not work?

Dim strstate As String

strtate = ActiveCell
ActiveCell("a4").Select
Do
If strstate = ActiveCell.Offset(-1, 0) Then
ActiveCell.Offset(1, 0).Select
Else
rowSelection.Insert Shift:=xlDown
ActiveCell.Offset(3, 0).Select
Loop Until ActiveCell.Offset(2, 0) = Empty

End Sub

Jaz, give this a try

Sub Insert_row()
Application.ScreenUpdating = False
Dim Rng As Range
Dim x As Long
Set Rng = Range("A4:A" & Range("A65536").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value < Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).Resize(1, 1).EntireRow.Insert Shift:=xlDown
End If
Next x
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Jaz" wrote in message
...
Spreadsheet contains a data range from cell A4 that is
refreshed regularly with updated data. Column A contains a
list of cities and is sorted A-Z. I am trying to cretae
macor that will insert a blank row between each change of
city all the way down. Cuurent attempt as follows but does
not work?

Dim strstate As String

strtate = ActiveCell
ActiveCell("a4").Select
Do
If strstate = ActiveCell.Offset(-1, 0) Then
ActiveCell.Offset(1, 0).Select
Else
rowSelection.Insert Shift:=xlDown
ActiveCell.Offset(3, 0).Select
Loop Until ActiveCell.Offset(2, 0) = Empty

End Sub

Try this



For x = 2 To 100000

If Range("A" & x) = "" Then Exit For
If Range("A" & x) < Range("A" & x + 1) Then
Range("A" & x + 1).Insert Shift:=xlDown
x = x + 1
End If

Next x




--

"Jaz" wrote in message
...
Spreadsheet contains a data range from cell A4 that is
refreshed regularly with updated data. Column A contains a
list of cities and is sorted A-Z. I am trying to cretae
macor that will insert a blank row between each change of
city all the way down. Cuurent attempt as follows but does
not work?

Dim strstate As String

strtate = ActiveCell
ActiveCell("a4").Select
Do
If strstate = ActiveCell.Offset(-1, 0) Then
ActiveCell.Offset(1, 0).Select
Else
rowSelection.Insert Shift:=xlDown
ActiveCell.Offset(3, 0).Select
Loop Until ActiveCell.Offset(2, 0) = Empty

End Sub