Is there a way to refine this code to make it faster? MS Excel VBA

I have a large worksheet of records which I need to parse though and copy into individual records.  When done, I believe it will be about 180,000 lines.  It runs fine with smaller segments of data, but when I kick it off on the actual data, it hangs up.

My code is rude...I'm certain there are ways to improve it, but I wouldn't know them.  I guess this is finger painting to all you artists out there.  Any help is appreciated.

File is attached with about 600 Lines of data.  The actual file has 16,000.
Attribute-Stripper-Sample.xlsm
MCaliebeAsked:
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.

Dale FyeCommented:
I'm not going to run your code, because I have not had time to check it for bugs.

Do you have access to Microsoft Access (it is in the full Office Professional Suite)?
This is the type of thing that Access was created for, and which should be used for this type of data anyway, instead of Excel.

This process would be simple and take almost no time whatsoever to perform in Access with a couple of queries.
0
MCaliebeAuthor Commented:
I have Access, and I plan to use it in access.  I just have no idea how I'd begin to set up queries to do this.  I would love nothing more than to dump this and move it into access and parse it out to records.
0
Roy CoxGroup Finance ManagerCommented:
Ca you explain in more detail what you your copying to Families and attributes.

There are some lines like -------
, can they be removed?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Dale FyeCommented:
Is this a process you will need to do on multiple occasions (daily, weekly, monthly)?
What version of Office do you have?

First step is to get this data into Access.

1.  Open a new Access database
2.  On the ribbon, there is an option (External Data).  Click on that,then click on the Excel button within the Import section of the Ribbon.  This will open the Get External Data wizard.
3.  Click Browse and select the file you want to load into Access.  Then click the "Link to the data source" radio button and the "OK" button.
4.  On the next page, select the sheet where your data is located and click "Next"
5.  Since you have a header row, check the "first row contains column headings" checkbox. Then click "Next"
6.  On the last screen, it will ask you to name your linked table.  For ease of use, just call it tbl_XL.

When you are done with this, you will have a linked table.  In the data you provided, there must be some "ghost" data, maybe functions or data that was deleted but not cleared, so there are extra columns and rows in the linked table.  I would go back to your spreadsheet highlight all of the columns to the right of "Material" and use "Clear All".  Then go to the row below your last line of data, select all of the rows below that and again select "Clear All"

Unfortunately, you have section separators and header rows inter-spaced throughout your data.  Because of this, Access interprets each of your columns as text field, which is probably correct for all but the List Price, L Lgth, and C Hex columns, we will take care of that later.

On each of those header rows, you have a numeric value in the left hand column, do you need to record that value for each of these sections?

At this point, I'm trying to determine why you think you need to separate the descriptions from the part attributes, this is how normal databases are organized.
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
[ fanpages ]IT Services ConsultantCommented:
You do not need to convert/move the source data into MS-Access in order to execute (SQL syntax-based) queries upon it; it can stay in MS-Excel (& code to manipulate the data with SQL statements can run within MS-Excel Visual Basic for Applications).

However, it may help me (us) if you could summarise what processing is required upon the data you have attached above; describe the "input" to the process, & how the "output" is derived from this.

Thank you.
0
MCaliebeAuthor Commented:
Thanks everyone for your input.  

As for the current code, I did find a condition which created an infinite loop which I corrected.

As I said, I didn't know how to attack this with Access, so Excel's ability to move through the data was my choice at the time.  Excel creates three sheets which become tables in Access.  Families, of which there are about 1000.  This is the first field in each segment, "A1" in the first series.  This will relate to the second table, Attributes, the data right of each family in the segments.  These vary by Families.  Finally, the Attribute data; this consists of the Item PN, "A2" in the first series, it's associated data to the right, the attribute for which this data is associated, and the family.

This structure seems to give me the flattest, most normalized and relatable structure I could determine.  As the raw data has about 1000 families, it didn't make sense to try to have access hold 1000 individual tables.  I could easily pull the Item PN and data on each line into a table, but it would be meaningless unless I knew what attribute each data variable was for.

I have another 20,000 items to consider in the future so it's worth me putting in the time now to get a working schema.

I know this question has evolved from refinements to my code, to alternate methods of retrieving the data.  I hope this helps and I appreciate the advise all you experts have to offer.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.