 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Macro to run daily and delete blank lines as well as duplicate entries.
Hi all, I get a new a spreadsheet every day. It has the month in the filename. This month's spreadsheet is called "June data". In that spreadsheet, there are tabs for each day of the month. The first tab is called 1, the second is called 2 and so on. The source spreadsheet ("June data") and the first tab ("1") has more data than I need. I just need to copy data in 4 columns to a destination spreadsheet. At the moment this is called Revenue Tracker. The Macro is saved in Revenue Tracker. I have already written a Macro to do this, but this is where I'm stuck. My Macro will copy the data in the 4 columns from Line 2 through Line 125 and paste that data to a destination spreadsheet ("Revenue Tracker"). The data has lots of blank lines as well as duplicate entries. I want to remove the blank lines as well as any duplicate entries. The source file is made from a template so it always ends at Line 125 but can have any numbers of entries. So the tabs numbered 1 through 30 all end at Line 125. The questions: How do I remove those blank lines? How do I delete the duplicate entries? Can I use Loop (or a similar function) to run these steps for each day, rather than write code for each day of the month? (i.e. Day 1 would use up lines 2 through 126, and day 2 would use lines 127 through 252, etc). Today is the 27th. If I only need to run the Macro for any days that haven't been copied so far. I don't really need to start at Day 1 every time I run the Macro. Your help would be greatly appreciated, Art. This will tidy up the source before you do your stuff Set oWb = Workbooks("June data".xls") For Each sh In oWb.Worksheets iLastRow = sh.Cells(sh.Rows.Count,"A").End(xlUp).Row For i = iLastRow To 1 Step -1 If sh.Cells(i,"A").Value = "" Then sh.Rows(i).Delete If Application.Countif(sh.Cells(i,"A").Value, sh.Columns(1),0) > 1 Then sh.Rows.delete End If Next i Next sh  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hi all, > [quoted text clipped - 38 lines] > > Art. Thank you for replying Bob, I tried the code below and these 2 lines won't run. If Application.Countif(sh.Cells(i,"A").Value, sh.Columns(1),0) > 1 Then Any ideas? Thanks, Art. Sorry for the delay, I was away for a few days. > This will tidy up the source before you do your stuff > [quoted text clipped - 57 lines] >> >> Art. That should be one line. The NG has wrapped the code around, so move the 1 Then to the end of the previous line. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Thank you for replying Bob, > [quoted text clipped - 73 lines] > >> > >> Art. Will do. Thanks. > That should be one line. The NG has wrapped the code around, so move the 1 > Then to the end of the previous line. [quoted text clipped - 88 lines] >> >> >> >> Art. I tried it and I'm getting a compile error. "End if without block if" The code now looks like this: (I'll keep it short so the ng doesn't wrap it) Set oWb = Workbooks... For Each sh In o... iLastRow = sh.Ce... For i = iLastRow To... If sh.Cells(i, "A").... If Application.Count... End If Next i Next sh End Sub Thank you for your help, Art. > That should be one line. The NG has wrapped the code around, so move the 1 > Then to the end of the previous line. [quoted text clipped - 88 lines] >> >> >> >> Art.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.