Updating excel range with dynamic price data

Hi

I have a function that gets me the last bid price of a stock and displays it in a single worksheet cell. It's inside some timer code so it gets the price each second.

But what if I want to show the last 10 bid prices in

the range("a1:a10")

, and when the 11th came in all the values would move up a row, so I see just the last 10.

I was thinking something like the following - but obviously I can't get it working :=(

m_BidPrice = myArray(i) 'put the bid into the array
i = i+1 
If i > 10 then somehow move all the values up 1 in myArray()
select the sheet
range("a1:a10")=myArray

Any suggestions would be great - thanks

You can try to use this code:

Private Sub Macro1()
   Dim myRow As Long, lastRow As Long
   
   lastRow = Cells(Rows.Count, "a").End(xlUp).Row
   
   If lastRow > 9 Then
      For r = 2 To 10
         Cells(r - 1, "a") = Cells(r, "a")
      Next r
      myRow = 10
   Else
      If Cells(1, "a") = "" Then
         myRow = 1
      Else
         myRow = lastRow + 1
      End If
   End If
   Cells(myRow, "a") = bid_price 'new entry
End Sub

Regards,
Antonio