What I need to do is, each week, I post 5 data rows for previous month and 5 rows for current month. Once we get to the new month, the “Previous Month” becomes static and the Current Month then becomes Previous Month, thus adding 5 new rows and so on.
Each week the first range will begin 10 rows from the end of the data set and the second range (Current MTD) will be written 5 rows from the end.
How can this be accomplished? Thanks for any help. I searched around and couldn’t find anything on this.
Let me offer an image. So the data here, 3/1 no longer changes, but since we are in May I am pasting April and May data into the file. Each week these numbers change. Next month I’ll add 6/1 data and continue posting 5/1 data (4/1 is locked in). So each week I just write over the data. Then with a new month we are expanding the number of rows, to include the new month.
I will be able to do a IF/THEN (if month is current), then I’ll want to write ranges into Row 9 and Row 17. So that number before the end. Yes, on months where we get fresh monthly data I can just do an append there.
In essence I just need to figure out how to assign the beginning of a “write range” X number of rows before the ending row.
I don’t see anywhere a write range would start X number of spaces before the end row. Depending on condition I need the write range to start in my file 16 rows before the end row and then another write range 8 rows. Am I missing that here?
it seems that it should be easy to simply write a data table into Excel X number of rows before the last row in a worksheet. Yet, nothing seems to exist out here.
So at the start of month you want to include new month and from next you need to over write the previous month as well with current month and one next month starts as new roes come in you would need to add few more and edit only half the number
So one thing you can do is read the data into datatable first ..and check the date in last row if the month from the date matches current month then consider removing last 16 rows and then the process of adding 16 rows remain same..on the else side as the month is not matching remove only 8 rows which would be previous month..and process of adding 16 rows remain same
If condition last row date matching with current month
True - delete 16 rows
False - delete 8 rows
After if add 16 rows current and previous as needed
So compare dates, if there is a match, delete 16 rows and append data table from my input file
If new date / month is seen, else to a delete of previous 8 rows and append 16 rows, adding the new month into the spreadsheet
I have never deleted last X number of rows in a spreadsheet but it can’t be hard. I’ll try to work on this today and get back and approve your solution. Again, thanks
Read the existing data into a datatable (Read Range). Filter Data Table to remove the rows you don’t want any more. Add the new rows. Delete the existing file and use Write Range to rewrite the file with the updated data from the datatable.
Whenever I’m working with Excel, I do it as read the data, manipulate the data in the datatable, then just rewrite the file. That’s easier than trying to remove/add etc directly in the existing Excel file.
I can’t argue with you there, however, the use case just never came together for me until I decided to invoke a simple piece of vba. Basically, if the dates match, delete 16 rows, if they don’t match delete 8 and proceed with appending the data table from my input file.
