VB script for Looping Formula

[dawsman]

I have been compiling a large excel db of data which currently stands at 365k rows and will grow substantially.

I am trying to copy a formula in a new column and understandably its crashing excel, so I thought creating a macro and getting it to copy the formula in smaller parts wouldn't stress it out too much (I could also set and forget it). Macro completed but I cant figure out how to loop it, I've read the MS guide and as a complete novice I cant figure it out. I have ordered a book from Amazon (on VBA and Access) but need this data ready this week.

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveCell.Offset(-25, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Range("A1:B1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:B63"), Type:= _
xlFillDefault
ActiveCell.Range("A1:B63").Select
End Sub

The macro is looking for an empty cell in column S&T and is copying two formulas down, be good for it to loop up until there is a space column R or by repetition (say 10 times).

Any help would be greatly received.

 

[VoG]

It isn't clear (to me) exactly what you want. Perhaps

Sub FillDown()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("S1:T1").AutoFill Destination:=Range("S1:T" & LR)
End Sub

 

[dawsman]

yes that almost it, I just need it to fill say 50 rows at a time and then start on the next 50. If I try and fill all the rows in at once it just crashes my machine (I'm presuming because it takes so much to process 350k rows of data)

I slightly amended the first code you gave me as the formula started on the 2nd row

Sub FillDown()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("S2:T2").AutoFill Destination:=Range("S2:T" & LR)
End Sub

Thanks for your help as reading back my initial post i wasn't exactly clear what I was asking either!

 

[sous2817]

Hello and welcome to the board!

Not test but try:

Sub FillDown()
Dim LR As Long
Dim i As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("S2:T2").AutoFill Destination:=Range("S2:T50")
For i = 51 To LR Step 50
    Range("S" & i & "T" & i).AutoFill Destination:=Range("S" & i & ":T" & i + 50)
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Keep in mind, w/ 350k rows and depending on the complexity of your formula it's going to take a long time for the sheet to calculate.

 

[VoG]

Perhaps

Sub FillDown()
Dim LR As Long, i As Long
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For i = 2 To LR Step 49
    Range("S" & i).Resize(, 2).AutoFill Destination:=Range("S" & i).Resize(50, 2)
Next i
End Sub

 

[sous2817]

You may also want to consider not looping through the whole range every time you run the macro. This will make the process quicker after the first run.

Something like (not tested):

Sub FillDown()
Dim LR1 As Long
Dim LR2 As Long
Dim i As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

With ActiveSheet
    LR1 = .Range("A" & Rows.Count).End(xlUp).Row
    LR2 = .Range("S" & Rows.Count).End(xlUp).Row

For i = LR2 To LR Step 50
    Range("S" & i & "T" & i).AutoFill Destination:=Range("S" & i & ":T" & i + 50)
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

This assumes that your longest column of data in in column A. The difference here is, the first time it runs it goes from 2 to 365k, which will take a long time. The next time it runs it would go from 365k to whatever the last row used in column A is, let's assume it's 400k. So only 35k lines of filling rather than 400k, which would be substantially faster.

 

[dawsman]

Thanks for the responses.

VoG - is there anyway the code can be set to have a time delay inbetween loops (10 seconds or something)? Just that running the code has meant excel has stopped responding so I cant do any other spreadsheet work.

Sous2817 - thanks for the snippets, I couldn't get the first one to work though as it came up with an error (sorry I didn't note it down and excel is trying chew through the data so I cant rerun).
As for the size, this will be a one off as the data gets added by a couple of thousand each time so wont be so labour intensive. I am trying to count unique values in a PT and have just realised that I need to formulate this in the data sheet rather than being able to complete in a PT, hence the backtracking.

 

[VoG]

Perhaps

Sub FillDown()
Dim LR As Long, i As Long
Application.Calculation = xlCalculationManual
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For i = 2 To LR Step 49
    Range("S" & i).Resize(, 2).AutoFill Destination:=Range("S" & i).Resize(50, 2)
    Calculate
    Application.Wait Now + TimeSerial(0, 0, 10)
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

 

[sous2817]

Sorry, guess I should do some testing before posting. Nonetheless, and not surprisingly, it looks like VoG gave you a working solution so I'll kindly bow out.

Best of luck with the rest of your project.

 

[dawsman]

Thanks for taking the time out to answer sous2817, I appreciate it!

Will try and run the filldown code overnight as I think its going to take time.

Will post an update in the morning.