re-organize data in fixed blocks
Thanks. worked well
anand
"akphidelt" wrote:
Alright, make sure you create a back up before trying this, but you can do it
with vba.
Go to, tools--macros---visual basic editor
Click on insert--- module
Copy and paste this code in to the module word for word.
Sub TestRows()
Dim MyCell
Dim x As Integer
Dim y As Integer
x = 2
Do Until ActiveCell.Row = 65532
Range("A" & x).Activate
MyCell = ActiveCell.Value
For y = 1 To 9
If Not ActiveCell.Offset(y, 0).Value = MyCell Then
ActiveCell.Offset(y, 0).EntireRow.Insert
End If
Next y
x = x + 10
Loop
End Sub
Make sure you click on cell A2 in the sheet. Then go back to the code and
press the little play button.
The code is set to the last possible row you can go to that ends in 2. IF
you have 45000 plus rows of data the its possible you will go over the limit
of rows.
Let me know how it works
"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
|