Solved

Compare current Excel workbook to next most recent and determine rows to delete

Posted on 2014-01-03
8
288 Views
Last Modified: 2014-01-03
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
Comment
Question by:BEBaldauf
  • 4
  • 2
  • 2
8 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39754111
Easiest way to accomplish this would be to have a creationdate field in wherever you're getting the data from.
0
 

Author Comment

by:BEBaldauf
ID: 39754140
I agree, but this data is coming from a third-party vendor who does not have this ability.
0
 
LVL 10

Expert Comment

by:etech0
ID: 39754146
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 35

Expert Comment

by:Kimputer
ID: 39754177
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
 

Author Comment

by:BEBaldauf
ID: 39754197
I can easily change the file naming to be yyymmdd if that would help automate...
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39754230
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
 

Author Comment

by:BEBaldauf
ID: 39754424
Works like a charm!  Thanks!!!
0
 

Author Closing Comment

by:BEBaldauf
ID: 39754425
Fast, slick, simple solution to my problem!  (wish I understood how it works!)  Thanks so much!!!!!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Outlook Free & Paid Tools
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question