Excel Automated Row Insertion

[mainepepmp]

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?

 

[CFSEng]

A macro could be written to do this. Can you be more specific as to what you need?

 

[mainepepmp]

I am glad to hear that I may be able to write a macro to do this; I was afraid I would need to develop a standalone VBasic script that wouldn't be bundled within the Excel workbook.

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.

 

[joerd]

This might be what you need. I didn't spend much time on it, so modify as you feel necessary. The code doesn't look at the first row (cell A1) so if that needs to be shifted down, you'll have to do it by hand.

Sub InsertBlankRows()
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

Variable i goes through all the numbers in column 1 (column A), n is the total number of rows, and a is a dummy variable that is used to calculate the number of rows to insert. Also, you'll get problems if column A contains blank rows between the entries - you may want to catch that (e.g. test for a zero value in Cells(i,1) - good practice ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.