Part of my VBA Code is a bottleneck, looking for a way to clean it up

[soneil9]

Hey everyone,

So I am by no means a VBA coder. What I have done hear is recorded macros and then manipulated the VBA code behind them. I have a particular piece of code that is acting as a bottleneck. What I'm trying to do is generate percentages from the cumulative total of a dynamic range in a column:

Sub CumulativePercent()Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1])"
Range("D3").Select
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End Sub

Does anyone have a better way of tackling this where it's not locking up my excel memory?

Thank you,
Sean

 

[Norie]

Sean

Don't know if it'll speed things up but it is a wee bit tidier.

Sub CumulativePercent()
    Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1])"
End Sub

 

[soneil9]

Norie,

Thanks for your input. Your code worked but it did not speed up the process.

Anyway, thank you again for your support.

-Sean

 

[Marcelo Branco]

Not sure i understand what you are trying to to

See if this is ok

Dim lastRow As Long

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2:D" & lastRow).FormulaR1C1 = _
"=RC[-1]/LOOKUP(2,1/(R2C3:R" & lastRow & "C3<>""""),R2C3:R" & lastRow & "C3)"

Hope this helps

M.

 

[soneil9]

Hello Marcelo,

This code seemed to work slightly better than my original, but excel is still locking up on me for about 30 seconds. I feel like there is still room for improvement. What I'm trying to do is calculate a percentage of the total for each row. The number of rows is dynamic in my workbook. If there was a way to find the last row and put the contents of that cell into a variable, and then fill down the formula for the entire column, that would be ideal. It seems like you have attempted to do that with your code. Am I right?

Thank you for your timely help!
Sean

 

[Norie]

Sean

Where is the total located?

Is it the last row in column C?

 

[dchaney]

soneil9

the code that Marcelo posted does exactly what you ask, lastRow = Range("C" & Rows.Count).End(xlUp).Row actually finds the last row with data in Column C and places that number into lastRow I think your issue is the adding of the Formula. try to do this,

[COLOR=#333333]Dim lastRow As Long[/COLOR]

Application.Calculation = xlCalculateManual

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2:D" & lastRow).FormulaR1C1 = [COLOR=#333333]"=RC[-1]/LOOKUP(2,1/(R2C3:R" & lastRow & "C3<>""""),R2C3:R" & lastRow & "C3)"

[/COLOR]Application.Calculation = xlCalculationAutomatic[COLOR=#333333]
[/COLOR]

It may be easier to just have all of these lines be populated by VBA instead of tossing a formula within them, especially if there are thousands of rows, the formulas alone will slow down the file.

 

[Marcelo Branco]

I think we're complicating something very simple

maybe...

Dim lastRow As Long

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2:D" & lastRow).FormulaR1C1 = "=RC[-1]/R" & lastRow & "C3"

M.

 

[dchaney]

I again think that no matter what he does, if the VBA is placing a formula within these cells instead of actually performing the functions then the sheet will be slowed down due to the calculation requirements each time a new cell is populated. Turnig off the calculations will allow the code to run faster but I think the sheet will be sluggish after the calculations are turned back on... I have not tested that, but I feel that is what you have here...


How many line items are there?

 

[Marcelo Branco]

I again think that no matter what he does, if the VBA is placing a formula within these cells instead of actually performing the functions then the sheet will be slowed down due to the calculation requirements each time a new cell is populated. Turnig off the calculations will allow the code to run faster but I think the sheet will be sluggish after the calculations are turned back on... I have not tested that, but I feel that is what you have here...


How many line items are there?

What i was trying to say with "complicating things" is since we have gotten the last row with data of column C (that , i suppose, houses the Total) and assigned it to the variable lastRow, there is no need of LOOKUP(2,1/...) in the formulas to be inserted in the rows. If there are thousands of rows, the LOOKUP part can slow down the processing.

M.