Variable Rows, Help Speeding Up

[Lelewiwi]

Hello,

I have the below piece of code that runs, but is extremely slow. I know the problem is the fact that it is looping through all 75000 rows. What I needs is, how do I change the code to just check the rows that actually have data in them? This is only a part of the overall code, I did not write this code, I am not a coder, I'm just trying to make it run faster.

' clean up acct#'s + populate Amounts
    ActiveSheet.Range("$A$1:$A$75000").RemoveDuplicates Columns:=1, Header:=xlYes
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(Sheet1!R1C3:R75000C8,'First Split'!RC[-1],Sheet1!R1C8:R75000C8)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B38")
    Range("B2:B38").Select
    ActiveWindow.SmallScroll Down:=-3
    
' paste acct#'s & months to new wksheet "Month Splits" + clean up data
    Range("B1").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Month Splits"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("A2").Select
    Sheets("Sheet1").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B2").Select
    Sheets("Sheet1").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    
' populate data
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(Sheet1!R2C8:R75000C8,Sheet1!R2C3:R75000C3,'Month Splits'!RC[-2],Sheet1!R2C5:R75000C5,'Month Splits'!RC[-1])" 
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C75000")
    Range("C2:C75000").Select

 

[Michael M]

Maybe this for the first section of your code....

Dim lr As Long
 ActiveSheet.Range("A1:A" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
 lr = Cells(Rows.Count, "A").End(xlUp).Row
    Range("B1").Value = "Amount"
    Range("B2:B" & lr).Formula = "=SUMIF(Sheet1!$C$1:$H$" & lr & ",'First Split'!A2,Sheet1!$H$1:$H$" & lr & ")"

you also need to eliminate the use of Select. Selection.
This

 Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Month Splits"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Account"

can be reduced to

 Sheets("Sheet3")..Name = "Month Splits"
    Range("A1").value = "Account"

 

[kevin9999]

There’s generally a number of ways of speeding up code execution, but to suggest something truly impactful would require seeing all of your code – not just what you’ve posted here. As @Michael M has already pointed out, there’s hardly ever a need to use ‘Select’, which in and of itself slows down code execution. Could you post your entire code?

Also, it would help if you could share your file via Google Drive, Dropbox or similar file sharing platform. You can easily disguise any sensitive/personal information. What would also help would be a description in plain English of what you’re trying to achieve with each step in your code.

 

[Lelewiwi]

I deleted most of the Select/Selection code and that worked. Thanks!

 

[kevin9999]

I deleted most of the Select/Selection code and that worked. Thanks!

It would be better if you posted your amended code - with all the selects removed - and mark that as the solution. That way, others could benefit from your improvements too.