BEBaldauf
asked on
Compare current Excel workbook to next most recent and determine rows to delete
I have a spreadsheet that I download weekly, which contains a growing dataset so each file contains the same rows as the previous PLUS any new rows created. Assuming I have the most-recent file open, I need to...
- Open the next-most-recent spreadsheet in a given folder
(Each file has a name that represents the date of its download, "12202013.xls" for example, if that helps identify the next most recent file)
- Count the number of rows in that file
- Go back to my most-recent file
- Delete that number of rows (counting from the top)
- Open the next-most-recent spreadsheet in a given folder
(Each file has a name that represents the date of its download, "12202013.xls" for example, if that helps identify the next most recent file)
- Count the number of rows in that file
- Go back to my most-recent file
- Delete that number of rows (counting from the top)
Last Comment
etech0
Easiest way to accomplish this would be to have a creationdate field in wherever you're getting the data from.
ASKER
I agree, but this data is coming from a third-party vendor who does not have this ability.
Aha. In that case you'd need a macro to accomplish what you need. I might be able to put something together later today, but let's see if anyone can help you meanwhile.
Some modifications might be needed.
It assumes all data is in Sheet(1)
It assumes Row A is always filled. And when an empty row is seen, it won't look further, even if there are more rows filled.
Also for now, YOU have to select the "newest" file. As your files do NOT have a logical filenames for ordering (ddmmyyyy is not the way to go, it's yyyymmdd if you need ordering), and I'm not sure if modification date is reliable. For now, you are the most reliable source to choose the file.
It assumes all data is in Sheet(1)
It assumes Row A is always filled. And when an empty row is seen, it won't look further, even if there are more rows filled.
Sub test()
sFileName1 = Application.GetOpenFilename
If sFileName1 = "False" Then
Exit Sub
End If
Set wb1 = Workbooks.Open(Filename:=sFileName1)
With wb1
countrows = 1
Do Until .Sheets(1).Cells(countrows, 1) = ""
countrows = countrows + 1
Loop
End With
wb1.Close
For i = 1 To countrows - 1
Sheets(1).Rows(1).Delete
Next
End Sub
Also for now, YOU have to select the "newest" file. As your files do NOT have a logical filenames for ordering (ddmmyyyy is not the way to go, it's yyyymmdd if you need ordering), and I'm not sure if modification date is reliable. For now, you are the most reliable source to choose the file.
ASKER
I can easily change the file naming to be yyymmdd if that would help automate...
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Works like a charm! Thanks!!!
ASKER
Fast, slick, simple solution to my problem! (wish I understood how it works!) Thanks so much!!!!!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
