Contact US

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!

*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

Autoinsert Rows

Autoinsert Rows

Autoinsert Rows

I have a large chunk of some production data - some 4000 lines and have it sorted by date. Is there a way to automatically segment the data so that I can put in a line or two under each date or to separate the data day-by-day so that I can minipulate certaion portions of the data?

RE: Autoinsert Rows

How would a couple blank lines in a 4000-line file help you any? You could write a macro takes each date's data and put it onto a separate sheet. Surely that would be more useful than blank lines?

How do you want to manipulate the data? Would a simple filter do the trick for you?

RE: Autoinsert Rows

Perhaps I've missed something here, but this is very easy to do in a VB Macro. If each row contains data for an individual date, the following code will insert 2 rows under each date ...

Sub insert_rows()

' Last row with data in it
last_data_row = 4000
' Number of rows to insert
For i = last_data_row To 1 Step -1
    Rows(i & ":" & (i + (numrows-1))).Select
    Selection.Insert Shift:=xlDown
Next i
End Sub

RE: Autoinsert Rows

This will do what you want provided, as you mention, the spreadsheet is sorted on column A.

This routine does the following:
1. Determine the last row used.
2. Working from the last row used upwards it compares
   the value of column A in the current row to column A in
   the row above.
   if they are the same nothing happens.
   If they differ then 2 rows are inserted.

Dim i As Long, LastRow As Long
Application.ScreenUpdating = False
'finds last used row in column A
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 1 Step -1
  If i >= 2 Then
    If Cells(i, "A") <> Cells(i - 1, "A") Then
      Rows(i).Insert   'add 1 blank row
      Rows(i).Insert   'add another blank row
    End If
  End If
Next i
Application.ScreenUpdating = True

RE: Autoinsert Rows

I use the ASAP "smart difference" function to insert formatting, blank lines or page breaks just as you desire.  I am not sure if it is allowed to mention specific companies, so feel free to red flag this if it is not appropriate.

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! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close