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)
BEBaldaufAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
Easiest way to accomplish this would be to have a creationdate field in wherever you're getting the data from.
BEBaldaufAuthor Commented:
I agree, but this data is coming from a third-party vendor who does not have this ability.
etech0Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

KimputerIT ManagerCommented:
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.

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

Open in new window


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.
BEBaldaufAuthor Commented:
I can easily change the file naming to be yyymmdd if that would help automate...
KimputerIT ManagerCommented:
In that case, adjust the folder var (include the last "\"!!!)
Again an assumption, being that folder only contains excel files. If not, I have to adjust code again.

Sub test()

    folder = "C:\test\"
    file = Dir(folder)
    tempfile = "0"
   While (file <> "")
      If file > tempfile Then
         tempfile = file
      End If
     file = Dir
  Wend
  
    
    Set wb1 = Workbooks.Open(Filename:=folder & tempfile)
     
    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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BEBaldaufAuthor Commented:
Works like a charm!  Thanks!!!
BEBaldaufAuthor Commented:
Fast, slick, simple solution to my problem!  (wish I understood how it works!)  Thanks so much!!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.