Code stop midstream

Looking at the attached excel file the page labeled 'Numbers_6' contains "XX DELETE" on some of the B lines. I believe the reason for this is that the script labeled 'Drop_Runs' or the script labeled 'RemoveSequentialNumberDups'  stopped midstream. I do not remember which since I had to restart to gain control again. I am inclined to believe that it was the "Drop_Runs" one since it contains an XX in the code but I am not sure since this is scripting and I am not familiar with scripting.

Another issue that I am having with the attached file is that it waits a minute or two to load. It may be my PC but I would just like some input from a fresh perspective on this.
MatchingCombinations.xlsm
Pedrov664Asked:
Who is Participating?
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.

redmondbCommented:
Hi, Pedro.

A few points...
(1) The file takes a minute to load simply because it's a very big file. So, make the file smaller and it will load faster. Instead of removing data (perhaps by splitting the sheets into separate files), you could reduce the file by 75% merely by saving it as an "Excel Binary Workbook (*.xlsb)". Not only is this dramatically smaller, but xlsb files are also more efficient to load (and save) than xlsx/xlsm files.
(2) The evidence is that Drop_Runs() did finish successfully. It processes the rows from the bottom up and reached row 409 - which is the last row it should have updated, so it did finish normally.
(3) For future reference, unless it's absolutely essential (which this wasn't), please don't post such large files. It would have been sufficient to extract the "Number_6" sheet and post that.

Regards,
Brian.
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
Pedrov664Author Commented:
Brian,

I have gotten so many requests for the 'original file' that caused the issue that I just decided to post the original here and let the techs decide what is needed and what is not.

Do the xlsb files contain the same data? I just did not know if changing the file type would cut off some files or data.
0
redmondbCommented:
Pedro,

I have gotten so many requests for the 'original file' that caused the issue
I'm the only commenter and I certainly didn't ask for that! Do you have another question for this problem?

Do the xlsb files contain the same data? I just did not know if changing the file type would cut off some files or data.
xlsb files are functionally identical to xlsx/xlsm files. The only visible differences....
 - xlsb files are usually smaller - sometimes dramatically so.
 - As xlsb files mimic the way Excel holds file in memory, there is no conversion when opening or saving them, so they're faster at that.
 - While xlsb files are zip containers similar to those of xlsx/xlsm files, the files contained within them are in binary format and so, while it's trivial to extract them,  they can't then easily be read. (If you don't understand this, don't worry - it's irrelevant to you.)
 - You know that an xlsx file can't contain a macro, whereas (like an xlsm) an xlsb may or may not have one.

Since I installed Excel 2007 some years ago, I happily mix xlsb and xlsm files - if it's small I leave it as xlsm, otherwise I use xlsb. I'm now on 2010 and have never once had an issue.

Cheers,
Brian.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

[ fanpages ]IT Services ConsultantCommented:
Hi Brian,

Just for information...

I have gotten so many requests for the 'original file' that caused the issue
I'm the only commenter and I certainly didn't ask for that! Do you have another question for this problem?

There is a previous question here:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28223794.html ]

Brad also provided some code in this question:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28218842.html ]

BFN,

fp.
0
redmondbCommented:
Thanks, fanpages, but it appears that those questions (although related) are neither for this problem not contain requests for all the data.
0
[ fanpages ]IT Services ConsultantCommented:
No, sorry, they are not; I asked for the exact format of the data, but the links I provided were just highlighting that this is an ongoing project & it is likely that somebody else has asked for the data in the past.

I'll not intrude any longer.  You seem to have covered what I would have said in any respect.
0
redmondbCommented:
Thanks, fanpages.
0
Pedrov664Author Commented:
fanpages & redmond,

it seems that when I request a code and then have a problem with it. Some request the "original" file and so in an effort to speed things up I simply posted the file I was using as I used it. Now I know better, thus will only to include the relevant data.

Thank you for all your help,
Pedro
0
redmondbCommented:
Thanks Pedro. The good news is that it's in your interest as well, as a big file may discourage Experts from getting involved.
0
Pedrov664Author Commented:
Thanks
0
redmondbCommented:
Thanks, Pedro.
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
VB Script

From novice to tech pro — start learning today.