Is a macro ok?

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow - 1 To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'in the group, do nothing
Else
HowMany = Application.CountIf(.Range("a:a"), _
.Cells(iRow, "A").Value)
If HowMany < 10 Then
.Rows(iRow + 1).Resize(10 - HowMany).Insert
End If
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


anand wrote:

I have an array of data. Column A has a sequential, repeating numeric code.
Example
Column A
1
1
1
3
3
3
3
3
4
4
5
5
5
6

In the column, the codes are always sequential overall but can repeat up
to 9 times (anywhere from 0 to 9 X). For example, you could have 9
sequential iterations of "7" in the column before you get to "8". Or you
could just have 1 or no iterations.

Beside each code # in column A is data in columns B, C, D etc.

What I'd like to do is reset the data for each numeric code so that it
always starts on every 10th row. Which would mean that each block of data
would start for example on Row 2, 12, 22, 32,42.... But since each block of
data may have anywhere from 1 to 9 iterations, there will be blank rows where
ever necessary.
So a row sequence code above would become:
Column A
1
1
1
blank row
blank row
blank row
blank row
blank row
blank row
blank row
blank row
3
3
3
3
3
blank row
blank row
blank row
blank row
blank row
4
4
8 blank rows

You get the idea.

Any reasonably quick way to do this? I have almost 45000 rows to do so a
fast way to do would be greatly appreciated.

Thanks

anand

--

Dave Peterson