Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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)
0
BEBaldauf
Asked:
BEBaldauf
  • 4
  • 2
  • 2
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
KimputerCommented:
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now