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
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
ASKER
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.
Ca you explain in more detail what you your copying to Families and attributes.
There are some lines like -------
, can they be removed?
There are some lines like -------
, can they be removed?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.