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 theGetLastRow() function would be more efficient.

I prefer to have a single function return the range object. Using theWorkshetFunction.Match() is far more efficient then iterating over the cells.

Results

getDateRange:Function

'Enumerations to clarify column data contentPublic Enum DataColumns    dcStocks = 1    dcDates    dcTimes    dcValuesEnd Enum' https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.matchFunction 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 WithEnd 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) = ValuesEnd 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    NextEnd SubSub 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.SelectEnd 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 CandleStickDataReDim CandleStickData(1 To RowCount, 1 To 6)r = 0For 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))    NextNextWorksheets.AddRange("A1:F1").Value = Array("Stock", "Date", "Open", "High", "Low", "Close")Range("A2").Resize(RowCount, 6).Value = CandleStickDataDebug.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.

• \$\begingroup\$Wow that is awesome. 'Never thought about returning a range. THANK YOU. FYI: 200,000 rows are all for one stock so will need to call getDateRange() for each increment (like 15 minutes). I also LOVE the idea of passing in the starting row of the previous iteration so reduce the amount of data to go thru as we progress thru the chart. This is awesome and I need to look deeper into you suggestions. THANK YOU!\$\endgroup\$

  Ed Landau

  – Ed Landau

  2019-08-20 13:17:04 +00:00

  CommentedAug 20, 2019 at 13:17
• \$\begingroup\$Creating a Candlestick table from the data-set would take about 40 secs. Having 1 record per Candlestick would increase the performance by +15x and the storage capacity by 15x (assuming 15 records per Candlestick). Updating the table weekly would take far less time.\$\endgroup\$

  TinMan

  – TinMan

  2019-08-20 17:54:11 +00:00

  CommentedAug 20, 2019 at 17:54
• \$\begingroup\$I recommend using a scrollbar as opposed to the mouse wheel. You would have to use WinApi calls to hook the mouse wheel.\$\endgroup\$

  TinMan

  – TinMan

  2019-08-20 17:55:51 +00:00

  CommentedAug 20, 2019 at 17:55
• \$\begingroup\$TinMan: Would you be willing to consult for an hour? 'Willing to pay :) If so, my temporary throw-away email, valid for the next few days is...TempAug20Ed@gmail\$\endgroup\$

  Ed Landau

  – Ed Landau

  2019-08-21 02:43:42 +00:00

  CommentedAug 21, 2019 at 2:43

• vba
• excel