Excel Automated Row Insertion
Excel Automated Row Insertion
(OP)
I am trying to get Excel to automatically insert blank rows between rows of data, in proportion to the numerical difference between the value of the same field in successive rows.
Any insights?
Any insights?





RE: Excel Automated Row Insertion
RE: Excel Automated Row Insertion
My worksheet is a list. The list elements appear vertically in rows of the worksheet.
The list elements contain different attribute groups.
Each attribute group may contain anywhere from one to 90 attributes, though the same combination of attributes aren't likely to appear in any two groups. (Each group has a different set of attributes.)
I guess my list is essentially a nested table?
In column A of my worksheet, numbers appear which indicate the attribute numbers, and they range from 1 to 90. (I call these the attribute indices.)
I need to align each group of attributes vertically, such that each is spread across 90 rows.
Rows with assigned attribute indices will contain their respective index number, and the remainder of the information about that attribute.
Rows without assigned attribute indices will be blank.
I could manually insert blank rows between non-successive attribute indices, but this would be inefficient and take too long.
I want to automate Excel to do the insertion of the blank rows, and have it insert the right number of blank rows such that I end up with 90 total rows for each group.
(I'm going to have Excel compare the information in this worksheet to a standard form template in the worksheet beside it, then flag exceptions a the third worksheet, via the (IF...) formula and Conditional Formatting.)
If you feel that the detailed instructions around how to code/develop the macro are too involved for this medium, or simply don't have the time to do my thinking for me, please feel free to simply provide guidance to point me in the right direction.
I know how to record Excel macros, but don't know how to make then execute the functional equivalent loops, For/Next statements, If statements, etc. that I would need in order to effect what I seek to do.
RE: Excel Automated Row Insertion
CODE
Dim i As Long, n As Long, a As Long
i = 2
n = ActiveSheet.UsedRange.Rows.Count
Do While i < n
a = (Cells(i, 1).Value - Cells(i - 1, 1).Value)
If a > 1 Then
Range(Cells(i, 1), Cells(i + a - 2, 1)).EntireRow.Insert
i = i + a - 1
n = n + a - 1
ElseIf a < 1 Then
a = (90 - Cells(i - 1, 1).Value) + Cells(i, 1).Value
Range(Cells(i, 1), Cells(i + a - 2, 1)).EntireRow.Insert
i = i + a - 1
n = n + a - 1
End If
i = i + 1
Loop
End Sub
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.