I have a 17,000 row excel 2007 spread sheet that looks like this:
PHP Code:
A B C D E F G H
DATA 2000 2003 Isuzu Trooper DATA DATA DATA
DATA 1996 1999 Isuzu Oasis DATA DATA DATA
DATA 2003 2004 Isuzu Ascender DATA DATA DATA
I want it to look more like this:
PHP Code:
A B C D E F G H
DATA 2000 Isuzu Trooper DATA DATA DATA
DATA 2001 Isuzu Trooper DATA DATA DATA
DATA 2002 Isuzu Trooper DATA DATA DATA
DATA 2003 Isuzu Trooper DATA DATA DATA
DATA 1996 Isuzu Oasis DATA DATA DATA
DATA 1997 Isuzu Oasis DATA DATA DATA
DATA 1998 Isuzu Oasis DATA DATA DATA
DATA 1999 Isuzu Oasis DATA DATA DATA
DATA 2003 Isuzu Ascender DATA DATA DATA
DATA 2004 Isuzu Ascender DATA DATA DATA
PHP Code:
Here is the macro I created to do this:
Dim year1 As Integer
Dim year2 As Integer
Dim counter As Integer
Range("B1").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
year1 = ActiveCell.Value
year2 = ActiveCell.Offset(0, 1).Value
counter = year2 - year1
ActiveCell.Offset(0, 1).Value = ""
Do While counter > 0
ActiveCell.EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Value + 1
counter = counter - 1
Loop
Loop
End Sub
The problem is that after the first 100 or so lines the macro slows down to 1 line every 1 second. After 400 lines is slows to 1 line every 2 seconds. I am guessing that I will have around 300,000 lines after everything is said and done. At the rate this thing is running it will take a year to finish. Is there a better way to acomplish what I need?
Bookmarks