Hi guys,
I was wondering if someone could help me out with this,
I am trying to tidy up some price files but it is taking forever to do as i have hundreds of thousands of rows to do, so i was looking at some ways to speed it up a bit.
Basically in any given row, i have a part for a motorbike, in one column, there is a year range, for example, 98-04, (this can range from 1950-2018) there is also cost, retail price, part number etc,
So far i have a vba to insert rows based on a cell value i have manually worked out from the year range 98-04 so in cell A1 for example i type the number 6, so when i run the vba it adds 6 rows below A1, which also takes a long time
So in an ideal world, i would like to have a vba that will do the following to the attached file:
-look at cell C1, which contains 95-01, i need it to add 6 rows below ( for 1996,1997,1998,1999,2000 and 2001) as the original row will be the 1995 row, and if possible fill in these year values iin their respective rows ie, 1995 in C1, 1996 in C2, 1997 in C3 etc
-and then once that is done if it could fill in all the blanks that are created with exactly what is in the respective rows above excluding the year column, so the example above, there will be 7 identical rows with the only difference being the year.
I have tried to do this with highlighting a column pressing F5,alt+s, alt+k, then ctrl+enter, however if there is a blank cell, for example I10 is supposed to be blank, so i dont want it to be filled with the value from I9
I think that's it if its possible to do, the following is the vba i have been using to start with (for this to work a new A column needs to be added to the attached file with the number of rows to insert,
Anyone's help would be greatly appreciated,
thanks in advance
regards,
Warren.
************************************************************************************
Public Sub Insert()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
lastrow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Cells(lastrow, 1).Select
Set CurrentCell = ActiveSheet.Cells(lastrow, 1)
For n = lastrow To 0 Step -1
If n = lastrow Then GoTo CheckLastRow
If n = 1 Then GoTo CheckfirstRow
ActiveCell.Offset(-2, 0).Select
CheckLastRow:
Set NextCell = CurrentCell.Offset(-1, 0)
ActiveCell.Offset(1, 0).Select
For i = 1 To CurrentCell
ActiveCell.EntireRow.Insert
Next i
Set CurrentCell = NextCell
Next n
'To be performed on the firstrow in the column
CheckfirstRow:
ActiveCell.Offset(-1, 0).Select
For i = 1 To CurrentCell
ActiveCell.EntireRow.Insert
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
******************************************************************************
Bookmarks