Generating Horizontal autofill date lists from a vertical set of start and finish dayes

armchairandy

Board Regular
Joined
Mar 27, 2012
Messages
53
Hi I have a set of dates in the first 2 columns, from which I fill all the dates between. Results shown at the bottom. Problem is that it is taking too long to process. For30 set of dates it took 8 minutes, and I could have up to 12,000 sets of dates at any one time. I have tried looking for autofill coding but they only deal with vertical. Also as the set of dates could be anything between 200 lines to 12000 lines using formulas in the worksheet will not work as the excel file gets too large. The current code I am using is:
Dim r As Range
Dim lastRow As Long
'
With Sheets("Sheet4")
' find last row with data in Column J ("Early Start Date")
lastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
'Col Q
For Each r In .Range("J13:J" & lastRow)
If Range("Q12") > Range("O2") Then
Exit Sub
End If
If r.Value <> "" Then
r.Offset(0, 7) = "=IF(RC[-2]>=R12C17,RC[-1]+1,0)"
End If
Next r
and so on until

End With
End Sub

the code continues for 120 columns,but the vba stops when the column matching the largest duration (O2) is met - There is a line along the top with sequential numbers representing the duration
Ths does work,but for 30 sets of dates it took 8 minutes



Any help would really be appreciated.

Andrew
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top