Sleeper: Make macro more efficient

[leal72]

I've been putting together this code to properly sequence my data points. The code below does what I need it to do but it's a little slow, just over 1 minute to run through 300 rows of data. Columns A through D contain coordinates for and object and each row is one object. I need the code to find the closest point to the last point. Here's and example of how I need the coordinates to sequence. What should I try to hopefully reduce the amount of time it takes to run through the data?

12142


Sub Optimize()

Dim xVal As Double
Dim yVal As Double
Dim zVal As Double
Dim rVal As Double
Dim x As Long
Dim lastRow As Long
Dim endRange As Long
Dim rIndex As Long
Application.ScreenUpdating = False
lastRow = Sheets("90s_06").Range("Z1").End(xlDown).Row
endRange = Sheets("90s_06").Range("C" & lastRow).End(xlUp).Row
xVal = Sheets("90s_06").Range("A2").Value
yVal = Sheets("90s_06").Range("B2").Value
zVal = Sheets("90s_06").Range("C2").Value
rVal = Sheets("90s_06").Range("D2").Value
For x = 1 To endRange
Sheets("optimized").Range("A" & x).Value = xVal
Sheets("optimized").Range("B" & x).Value = yVal
Sheets("optimized").Range("C" & x).Value = zVal
Sheets("optimized").Range("D" & x).Value = rVal
rIndex = Sheets("90s_06").Range("G2").Value
'Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDIRECT(ADDRESS(RC[-1],1,1,0),FALSE)"
Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDEX(C[-7],RC[-1])"
Sheets("90s_06").Range("I2").FormulaR1C1 = "=INDEX(C[-7],RC[-2])"
Sheets("90s_06").Range("J2").FormulaR1C1 = "=INDEX(C[-7],RC[-3])"
Sheets("90s_06").Range("K2").FormulaR1C1 = "=INDEX(C[-7],RC[-4])"
xVal = Sheets("90s_06").Range("H2").Value
yVal = Sheets("90s_06").Range("I2").Value
zVal = Sheets("90s_06").Range("J2").Value
rVal = Sheets("90s_06").Range("K2").Value
Sheets("90s_06").Range("A2:D2").ClearContents
Sheets("90s_06").Range("A1").Value = xVal
Sheets("90s_06").Range("B1").Value = yVal
Sheets("90s_06").Range("C1").Value = zVal
Sheets("90s_06").Range("D1").Value = rVal
Sheets("90s_06").Range("A" & rIndex, "D" & rIndex).ClearContents
Next
End Sub

[Aussiebear]

You're changing the x,y,z,r, values a lot during the code, is that necessary?

[leal72]

You're changing the x,y,z,r, values a lot during the code, is that necessary?

most likely that it's not necessary but it is the only way, so far, I've been able to get the result I'm looking for.

With the first instance of x,y,z,r I want that to be my base point

In the loop, I need the base added to the "optimize" sheet, this is my summary sheet. Next instance, I'm finding the location of the coordinate the closest to the base point. Then, because of how we want the sequence result, the closet point must become our new base point.

[leal72]

Went back and removed the x,y,z,r completely and was able to run through the 288 rows in under 3 seconds, much better. Thanks, likely wouldn't have looked at that without your comment. :thumb


Sub Optimize()

Dim x As Long
Dim lastRow As Long
Dim endRange As Long
Dim rIndex As Long


Application.ScreenUpdating = False

lastRow = Sheets("test").Range("Z1").End(xlDown).Row
endRange = Sheets("test").Range("C" & lastRow).End(xlUp).Row

Sheets("test").Range("A1").Value = Sheets("test").Range("A2").Value
Sheets("test").Range("B1").Value = Sheets("test").Range("B2").Value
Sheets("test").Range("C1").Value = Sheets("test").Range("C2").Value
Sheets("test").Range("D1").Value = Sheets("test").Range("D2").Value
Sheets("test").Range("A2:D2").ClearContents


For x = 1 To endRange - 2
Sheets("optimized").Range("A" & x).Value = Sheets("test").Range("A1").Value
Sheets("optimized").Range("B" & x).Value = Sheets("test").Range("B1").Value
Sheets("optimized").Range("C" & x).Value = Sheets("test").Range("C1").Value
Sheets("optimized").Range("D" & x).Value = Sheets("test").Range("D1").Value
rIndex = Sheets("test").Range("G2").Value
Sheets("test").Range("H2").FormulaR1C1 = "=INDEX(C[-7],RC[-1])"
Sheets("test").Range("I2").FormulaR1C1 = "=INDEX(C[-7],RC[-2])"
Sheets("test").Range("J2").FormulaR1C1 = "=INDEX(C[-7],RC[-3])"
Sheets("test").Range("K2").FormulaR1C1 = "=INDEX(C[-7],RC[-4])"
Sheets("test").Range("A1").Value = Sheets("test").Range("H2").Value
Sheets("test").Range("B1").Value = Sheets("test").Range("I2").Value
Sheets("test").Range("C1").Value = Sheets("test").Range("J2").Value
Sheets("test").Range("D1").Value = Sheets("test").Range("K2").Value
Sheets("test").Range("A" & rIndex, "E" & rIndex).ClearContents
Next

End Sub