VB script for Looping Formula

dawsman

Board Regular
Joined
Jul 20, 2010
Messages
55
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It isn't clear (to me) exactly what you want. Perhaps

Code:
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

Board Regular
Joined
Jul 20, 2010
Messages
55
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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Hello and welcome to the board!

Not test but try:

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Perhaps

Code:
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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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):
Code:
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

Board Regular
Joined
Jul 20, 2010
Messages
55

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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

Board Regular
Joined
Jul 20, 2010
Messages
55
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,805
Messages
5,598,173
Members
414,217
Latest member
Mystix

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
Top