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,081,777
Messages
5,361,231
Members
400,618
Latest member
Gamaway

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top