How to create a continuous VBA program sub

[tdsec1]

I am uploading live-data from bloomberg onto excel, and that data contains the date/time (i.e. 7/8/14 6:01pm) and a stock price. Since it goes in real time, after each previous date/time and stock price, whenever new data becomes available, it is constantly being added below the previous date/time and stock price.

My question is: is there possibly a way that whenever the new data gets added that the previous data gets deleted, and how would I do it?
(i.e. if new data comes in as 7/8/14 6:02pm, then "7/8/14 6:01pm" is deleted)

Thank you very much in advance!

 

[ozbod]

I assume there is more than these two data items ? eg StockCode, Date/Time, StockPrice,etc etc

Are you trying to
(a) Delete any lines with a date/time earlier than the lastest
(b) as with (a) but for each StockCode
(c) as for (a), or (b) but only earlier times in that day

 

[tdsec1]

I assume there is more than these two data items ? eg StockCode, Date/Time, StockPrice,etc etc

Are you trying to
(a) Delete any lines with a date/time earlier than the lastest
(b) as with (a) but for each StockCode
(c) as for (a), or (b) but only earlier times in that day

There are 2 items in each row. One is Date/Time and the other is stock price. After the end of each minute, a new row is added containing a new date/time and a new stock price (as it pulls new ones in from bloomberg. What I want to happen is that after each minute when the new row gets added, I want the old row to be deleted so that I only have the new row. So the answer to your question is (a).

 

[ozbod]

If that's the case then you could use the Worksheet_Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 2 And Target.Column = 2 Then 'Assuming a header row and wait for the price to be written
        
        For n = Target.Row - 1 To 2 Step -1 'for all prior rows (other than the header)
        
            Range(Cells(n, 1), Cells(n, 2)).Delete shift:=xlUp
            
        Next n
        
        Cells(1, 1).Select ' Return to A1
        
    End If
    
End Sub