Solved

Excel Macros : Parsing multiple date ranges in a row

Posted on 2012-03-28
6
407 Views
Last Modified: 2012-03-28
Hi,

I’ve created a series of macros to parse out some date ranges.  I’m on the last part of it and I’m not able to get any farther.   In each row, I need to compare a series of date ranges which are parsed out into columns like this:

 ISSN  | start date 1 | end date 1 | start date 2 | end date 2 | and so on . . . .

Example data:

5555-5555 | 1976  | 1982 | 1984 | 1989 | 1978 |1981 | 1987 |2010

The example above needs to be parsed to the following:

555-5555 | 1976 |1982 | 1984 | 2010

Once a row is fully parsed, it’s moved to another sheet.

The macro runs fine for the first 2000 or so rows but then I get a Method ‘Delete’ of object ‘Range’ failed message.  I’ve tried many solutions and just can’t figure this out.  I need to eventually parse approx. 5,000 in total.

I’ve attached my excel workbook and then a text file that contains the macros. (I didn't think we could attach a macro-enabled workbook)  In my workbook, my macro lives in Module 1.  The data I’m parsing is in the sheet called ISSN.  The sheet called final is where rows are moved when the row is finished being parsed.  

If you look at the macro, you’ll understand what I’m trying to do a little better.  It’s very hard to explain in words.  Also, this is my first experience with writing a macro, so please let me know if you think I should be approaching this a different way – although I have little time left to work on this as the deadline is approaching.  I really need to figure out a way to parse all these records.

Thank you in advance.
example.xlsx
example-macro.txt
0
Comment
Question by:Finlay11
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37778496
Rather than MOVE the rows, copy the rows and mark those rows for deletion...

E.g.,

dim rDelete as Range

  'in your loop, you delete a row, as with:

  ISSN.CELLS(i,"A").EntireRow.Delete

 'instead, do this:

 if rDelete is Nothing Then
     set rDelete = ISSN.Cells(i,"A")
 else
     set rDelete = Union(rDelete,ISSN.Cells(i,"A")
 end if

'then at the end of your loop, you can delete all those rows in one step

  rDelete.EntireRow.Delete

Open in new window


When you loop through a range, and delete rows at the same time, the index in the for each can get messed up, and if you're using a long index, you'd need to loop in a step -1 fashion from the bottom up, if you want to delete rows as you process.  Otherwise, just marking a range for deletion (as the suggested code, above does) is your best approach, IMHO.

When I look at your code, I can't find your loop structure, so assume you didn't disclose all your code (which would be better shared in an actual .xlsm workbook perhaps - yes you can upload it, or actually displayed in your question.)

Good luck and hope this helps!

Dave
0
 

Author Comment

by:Finlay11
ID: 37778769
Hi Dave,

Thanks for commenting.  I actually am NOT using a loop.  I'm using Cell(C1) to keep track of the rows.  I think it will become clearer if you wouldn't mind taking a look at the new workbook I just uploaded.  I would really appreciate it.
example.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37778773
I saw that when I pulled your code together with the workbook.  I did see the error you got and am looking at it.

Will revert shortly - have to run to a couple meetings but will revert back.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:dlmille
ID: 37778839
PS - can you describe what/how you're parsing - reading through all your code doesn't make it clearly apparent, and your original post leaves much to be desired as compared to your dataset?

You are making recursive calls to the different subs, rather than looping and that's why the application is crashing - running out of stack space...

Let me see if I can do a quick rewrite, but if you want help on the code itself, you'd need to better describe what you're trying to do.

My effort right now is to get you through what you already have.

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37778900
Ok - to avoid the out of stack space error, and your original posted error, your code really can be rewritten (on an as-is basis) converting all 3 modules into 1 module, and using GoTo's to navigate where in the code you want to go next.  So the logic behavior should be exactly the same, except there are no recursive calls going on.

I ran this through to completion, successfully.

See attached solution (again, without rewriting your logic, just repairing what you have so it would run).  

I compared output of this solution with your original (stopping it before it crashed) and got a good match, so I think you are good to go!

If I understood more completely what you were doing/how you were processing (in words) - and what success (ending format) looked like - I might be able to help you re-code it, however this solution appears to be working correctly and if you're under the gun, perhaps we're dong.

Cheers,

Dave
Example-r2.xlsm
0
 

Author Comment

by:Finlay11
ID: 37779141
Dave,

I would love to know a better way to code this but I am pressed for time.  I'll award you the points.  Thank you so much for helping me out!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now