×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Automated Row Insertion

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?

RE: Excel Automated Row Insertion

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

RE: Excel Automated Row Insertion

(OP)
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.

RE: Excel Automated Row Insertion

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.

CODE

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 winky smile

Cheers,
Joerd

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources