How to create a continuous VBA program sub

tdsec1

New Member
Joined
Jul 8, 2014
Messages
3
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

Board Regular
Joined
Apr 2, 2008
Messages
177
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

New Member
Joined
Jul 8, 2014
Messages
3
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

Board Regular
Joined
Apr 2, 2008
Messages
177
If that's the case then you could use the Worksheet_Change Event


Code:
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
 

Forum statistics

Threads
1,085,566
Messages
5,384,476
Members
401,904
Latest member
markschneider89

Some videos you may like

This Week's Hot Topics

Top