Speed Up Macro

[katekoz]

I have a long macro that does many things and takes a really long time to run. I'd like to speed it up, but I'm not entirely sure which part is bogging it down. I have a guess that it's one of two places, as they're the two parts of the macro that "step through" data. I've copied each below. Does anyone have any tips on how I could speed these sections up, or could you let me know if they're just as good as they're going to get?

In this first section, the "Column 4" in "Overall User Data" that's being referenced currently has just over 10k rows of data. The "Column 4" in "New Data Add" is currently an empty set, so is checking 0 rows, and the most it will ever have is about 2k. "UserValue" is the value received from an inputbox earlier in the macro.

With ThisWorkbook.Sheets("Overall User Data").Columns(4)
    Set Fnd = .Find(UserValue, , , xlWhole, , , False, , False)
End With
If Fnd Is Nothing Then
    With ThisWorkbook.Sheets("New Data Add").Columns(4)
        Set Fnd = .Find(UserValue, , , xlWhole, , , False, , False)
    End With
End If

This second code is dealing with a little table, about 21 rows long, so I doubt this would be bogging it down too much, which such a small data set. Maybe it is, though??

Set ws = Sheets("All Users Month")

    lastRow = ws.Range("Y" & Rows.Count).End(xlUp).Row - 1

    With ws
        For i = 1 To lastRow
            If Len(Trim(.Range("Y" & i).Value)) <> 0 Then _
            .Range("Z" & (i + 1)).Formula = "=RAND()"
        Next i
    End With
    
    Worksheets("All Users Month").Range("Z:Z").Copy
    Worksheets("All Users Month").Range("Z:Z").PasteSpecial Paste:=xlPasteValues

If both of these look ok, then it must be some other part that's not running well. Thanks for any tips you can offer with this!

 

[Fluff]

probably won't make much difference, but try

   Set Ws = Sheets("All Users Month")

    lastRow = Ws.Range("Y" & Rows.Count).End(xlUp).Row - 1

    With Ws
        For i = 1 To lastRow
            If Len(Trim(.Range("Y" & i).Value)) <> 0 Then _
            .Range("Z" & (i + 1)).Formula = "=RAND()"
        Next i
        With .Range("Z1:Z" & lastRow)
            .Value = .Value
        End With
    End With

 

[Puertorekinsam]

be sure to include:

application.screenupdating=false
'your code
application.screenupdating=true

around your code

 

[katekoz]

Fluff - I tried the code change, but it didn't really do anything. I'm leaving it the way you sent, though, because I like the format of it better, so thanks for that!

Puertorekinsam - I did have my screenupdating turned off, but thanks for the suggestion.

I guess it's just a long macro that will take a while to run! Thanks for both of your help, though!

 

[Fluff]

How long does it take to run the macro?

 

[katekoz]

It takes about 55 sec to run. The thing is - it's just not that complicated of a macro. It does a whole lot of "find the last row, add a value to a cell, resort the table" throughout different sections of a workbook. It also creates a new sheet by copying a template sheet and renaming it. Maybe that's taking some time??

 

[DanteAmor]

For the second part
Try this

Sub test()
  Dim ws As Worksheet, i As Long, LastRow As Long, a As Variant, b As Variant
  Set ws = Sheets("All Users Month")
  
  LastRow = ws.Range("Y" & Rows.Count).End(xlUp).Row - 1
  a = ws.Range("Y1", ws.Range("Y" & LastRow)).Value2
  Randomize
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Len(Trim(a(i, 1))) <> 0 Then b(i, 1) = Rnd()
  Next i
  ws.Range("Z2").Resize(UBound(b)).Value = b
End Sub

The first part is inside a loop?

 

[katekoz]

Hi DanteAmor - Thanks for your help. I plugged in the code you gave me and it ran about the same amount of time.

The first part is not in a loop. It's a "with" that ends, followed by an "if" that ends. I can send all the code before what's entered in my original post if that would help. Perhaps I've messed something up?

I would post the whole macro, but it is almost 400 lines long, and I think that would be too much to ask to go through. And, as I mention above, the bulk of the macro is around finding a last line, pasting data, then sorting - over and over again.

 

[DanteAmor]

I would post the whole macro, but it is almost 400 lines long

Don't worry, put the full macro to see where it can be improved. And a sample of your data.

 

[Fluff]

Won't hurt to post the entire macro.