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?
 
KimputerConnect With a Mentor Commented:
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

0
 
etech0Commented:
Easiest way to accomplish this would be to have a creationdate field in wherever you're getting the data from.
0
 
BEBaldaufAuthor Commented:
I agree, but this data is coming from a third-party vendor who does not have this ability.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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.
0
 
KimputerCommented:
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.
0
 
BEBaldaufAuthor Commented:
I can easily change the file naming to be yyymmdd if that would help automate...
0
 
BEBaldaufAuthor Commented:
Works like a charm!  Thanks!!!
0
 
BEBaldaufAuthor Commented:
Fast, slick, simple solution to my problem!  (wish I understood how it works!)  Thanks so much!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.