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!

 

[offthelip]

You can speed up the 2nd part of your code by using variant arrays and calculating the random number in VBA: like this: (untested)

Sub test()
Set ws = Sheets("All Users Month")

    lastrow = ws.Range("Y" & Rows.Count).End(xlUp).Row - 1
    With ws
    'load column Y into a variant array
    yarr = Range(.Cells(1, 25), .Cells(lastrow, 25))
    'load column Z into a variant array
    Zarr = Range(.Cells(1, 26), .Cells(lastrow, 26))
   
        For i = 1 To lastrow
            If Len(Trim(yarr(i, 1))) <> 0 Then _
            Zarr(i + 1, 1) = Rnd()
        Next i
    Range(.Cells(1, 26), .Cells(lastrow, 26)) = Zarr
    End With
   
End Sub