Excel Macros : Parsing multiple date ranges in a row

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
Finlay11Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
Finlay11Author Commented:
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
dlmilleCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Finlay11Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.