add rows each x minutes to table wich fixed # of rows.

You can do what you want with formulas if you have a sheet that continues to accumulate new data at the bottom.

Create a new table with this formula in cell A2:

=INDEX(DataSheet!A:A,ROW(A1)+MAX(1,COUNTA(DataSheet!A:A)-299))

and drag down for 300 rows, and across for as many columns as you need. This assumes that your headers on DataSheet are in row 1, there are no gaps in your data, and the newest data is added to a blank row at the bottom of your sheet DataSheet.

If I got the logic backwards - post back.

Add code like this, that deletes values starting from the top of the sheet, leaving the header row and 300 rows of data on sheet Data. Start the code by calling CleanUp from the workbook open event, and call StopIt from the workbook before close event. The macro is run every 5 minutes, but you can change that to a longer time period if it doesn't bother you to have more data. You could really just do it every time you open the workbook, unless the workbook is never closed.

Please Login or Register  to view this content.

Yes - the macro is in place of the formula. It will work on one worksheet to keep the number of rows to 301 - 1 header row and 300 rows of data. No need for a second worksheet.

I don't see the tables - if they are all horizontally spread out and each is limited to 300 rows..... maybe an example workbook showing your table structure, and an explanation of what you really want....

The attached doesn't currently have the data connection but the code will start running when you open it, and run every 5 minutes thereafter. It will stop the process when you close the workbook.

I have changed the code to keep the tope two rows with the headers.

Let me know....