You are doing twice the work by having a function to get the starting row and a second function get the last row. Passing the starting row into the GetLastRow() function would be more efficient.
I prefer to have a single function return the range object. Using the WorkshetFunction.Match() is far more efficient then iterating over the cells.
Results
getDateRange:Function
'Enumerations to clarify column data content
Public Enum DataColumns
dcStocks = 1
dcDates
dcTimes
dcValues
End Enum
' https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match
Function getDateRange(ByVal StartDateTime As Date, ByVal EndDateTime As Date) As Range
Const LargestValueGreaterThanOrEqualTo As Long = -1
Const FirstExactMatch As Long = 0
Const LagestValueLessThanOrEqualTo As Long = 1
Dim Target As Range
With ThisWorkbook.Worksheets(1)
Set Target = .Range("A4:Z4", .Cells(.Rows.Count, dcDates).End(xlUp))
End With
Dim dates
Dim RangeStart As Long, RangeEnd As Long
Dim SearchValue As Double
SearchValue = StartDateTime - 1
On Error Resume Next
RangeStart = WorksheetFunction.Match(SearchValue, Target.Columns(dcDates), LagestValueLessThanOrEqualTo)
On Error GoTo 0
If RangeStart = 0 Then Exit Function
Dim r As Long
Dim StartFlag As Boolean
Dim DateTime As Date
With Target
For r = RangeStart To .Rows.Count
DateTime = .Cells(r, dcDates).Value + .Cells(r, dcTimes).Value
If DateTime >= StartDateTime And Not StartFlag Then
RangeStart = r
StartFlag = True
End If
If DateTime > EndDateTime Then
RangeEnd = r - 1
Exit For
End If
Next
If r > .Rows.Count Then RangeEnd = .Rows.Count
Set getDateRange = .Rows(RangeStart & ":" & RangeEnd)
End With
End Function
Worksheet Test Preparation
Sub Prep()
Const RowCount As Long = 260000
'https://codereview.stackexchange.com/questions/226360/vba-loop-optimization
Dim codes, dates, stocks, times, Values
Dim d As Date, t As Date
codes = Array("ACB", "AYI", "A2B", "ABP", "ABL", "AEG", "ABT", "AJC", "AKG", "AX8", "AX1", "ACS", "ACQ", "ACF", "ACR", "ACW", "AIV")
ReDim stocks(1 To RowCount, 1 To 1)
ReDim dates(1 To RowCount, 1 To 1)
ReDim times(1 To RowCount, 1 To 1)
ReDim Values(1 To RowCount, 1 To 1)
Dim r As Long, r2 As Long
d = #1/1/2010#
For r = 1 To RowCount - 48
d = d + 1
For r2 = 0 To 47
t = TimeSerial(0, r2 * 30, 0)
stocks(r + r2, 1) = codes(WorksheetFunction.RandBetween(0, UBound(codes)))
dates(r + r2, 1) = d
times(r + r2, 1) = t
Values(r + r2, 1) = Int((Rnd * 100) + 1) + Rnd
Next
r = r + r2 - 1
Next
Range("A4").Resize(RowCount) = stocks
Range("B4").Resize(RowCount) = dates
Range("C4").Resize(RowCount) = times
Range("D4").Resize(RowCount) = Values
End Sub
Test
Sub Main()
Dim Results(5) As String * 25
Const TestCount As Long = 10
Dim n As Long
Results(0) = "Date Range"
Results(1) = "StartDateTime"
Results(2) = "EndDateTime"
Results(3) = "MinPrice"
Results(4) = "MaxPrice"
Results(5) = "Time"
Debug.Print Results(0), Results(1), Results(2), Results(3), Results(4), Results(5)
For n = 1 To TestCount
Test
Next
End Sub
Sub Test()
Dim Results(5) As String * 25
Dim t As Double: t = Timer
Dim Target As Range
Dim d As Date, StartDateTime As Date, EndDateTime As Date
StartDateTime = WorksheetFunction.RandBetween(#1/2/2010#, #8/30/2024#)
EndDateTime = StartDateTime + TimeSerial(WorksheetFunction.RandBetween(1, 24) - 1, WorksheetFunction.RandBetween(1, 2) * 60, 0) + WorksheetFunction.RandBetween(1, 60) - 1
Set Target = getDateRange(StartDateTime, EndDateTime)
Dim MinPrice As Double, MaxPrice As Double
MinPrice = WorksheetFunction.Min(Target.Columns(4))
MaxPrice = WorksheetFunction.Min(Target.Columns(4))
Results(0) = Target.Address
Results(1) = StartDateTime
Results(2) = EndDateTime
Results(3) = MinPrice
Results(4) = MaxPrice
Results(5) = Round(Timer - t, 2)
Debug.Print Results(0), Results(1), Results(2), Results(3), Results(4), Results(5)
Target.Select
End Sub
CandleStick Chart
The dataset in the image shows that you need to know the Open, High, Low, and Close for each day to create the Chart. Considering there are over 200 K rows, I presume that you will also need to filter by stock. If this is true then I would take a different approach.
I would have a dictionary that stores a sub-dictionary for each stock that stores a dictionary for each day that stores an arraylist to store the values.
Create Array From Data Structure and Write it To New Sheet
Dim CandleStickData
ReDim CandleStickData(1 To RowCount, 1 To 6)
r = 0
For Each StockKey In StockMap
Set DateMap = StockMap(StockKey)
For Each DateKey In DateMap
Set ValueList = DateMap(DateKey)
r = r + 1
rowData = ValueList.ToArray
CandleStickData(r, 1) = StockKey
CandleStickData(r, 2) = DateKey
CandleStickData(r, 3) = rowData(0)
CandleStickData(r, 4) = WorksheetFunction.Max(rowData)
CandleStickData(r, 5) = WorksheetFunction.Min(rowData)
CandleStickData(r, 6) = rowData(UBound(rowData))
Next
Next
Worksheets.Add
Range("A1:F1").Value = Array("Stock", "Date", "Open", "High", "Low", "Close")
Range("A2").Resize(RowCount, 6).Value = CandleStickData
Debug.Print Round(Timer - t)
I did a quick mock up and it took 21 seconds to load 259,967 rows of data into the dictionaries and ArrayList and just 11 seconds to build a new Array and write it to a worksheet. After the data has been processed, it would be a simply matter of getting the date range and updating the chart table. Changing the stocks or chart data should take no more than 1 tenth of a second.
GetFirstRowandGetLastRowwith sql. \$\endgroup\$