?
Solved

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

Posted on 2014-01-03
8
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

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 36

Accepted Solution

by:
Kimputer earned 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 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