You may consider using FIND to find starting from the bottom going up p1 and
then p2 using 1 cell less. Then count the diff and do your thing.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ReportSmith" wrote in message
...
A 2-part question. I have code that will sort a a dataset (with multiple
rows and columns) by the data in column "J" (a STATE field).

Then, the code will cycle through the sorted list and everytime a state
changes, new lines will be entered to the next 1,000th (so if 'AK' has 700
rows, 300 blank lines will be entered before the 'AL' rows (which will
start
on row 1001; if 'AK' has 1100 rows, 900 blank lines willbe entered before
the
'AL' rows (which will start on row 2001, etc).

The code works, but not as fast as I would like to see. Does anyone have
any suggestions for a faster way?

Sub Test()
Cells.Select
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'go to the State column, row 2
Application.Goto reference:="R2C10"
PrevCell = ActiveCell.Value

RowNum = 1
Do Until PrevCell = ""
'find the next state
Do Until ActiveCell.Value < PrevCell
Application.Goto reference:="R" & RowNum + 1 & "C10"
RowNum = RowNum + 1
Loop

'find the next 1000th line
NewRow = Application.WorksheetFunction.Ceiling(RowNum, 1000)
'do until the next 1000th line
Do While RowNum <= NewRow
Rows(RowNum).Select
'select the row
Selection.Insert Shift:=xlDown
'insert blank line
RowNum = RowNum + 1 'go to
next row
Loop

'move 1 row down and column J
Application.Goto reference:="R" & NewRow + 1 & "C10"
PrevCell = ActiveCell.Value
Loop
End Sub

Thanks in advance for any/all suggestions.