Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

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
0
Finlay11
Asked:
Finlay11
  • 4
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Technology Partners: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now