Solved

Excel Macros : Parsing multiple date ranges in a row

Posted on 2012-03-28
6
408 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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