[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

1,000,000+ Rows And Simple Macro Keeps Bugging

Hi,

I have 2 worksheets, one with 1,000,000+ rows and the other with over 400,000. When I run a simple macro its extremely slow and gets stuck.

It is not a bugging error as the macro works when its a smaller file.

The macro works like this:

1. Analyzes another column within the same worksheet to find specific keywords and then label the associated category for each. E.g "ExpertsExchange.com", reference keywords to pick up from column a, experts,expertz,exparts, and output "Technology".

How do I improve the speed?
0
ahs_expert
Asked:
ahs_expert
  • 3
  • 2
  • 2
  • +4
5 Solutions
 
p_nutsCommented:
You'd be better of using a db for this even if it's access ..
0
 
ahs_expertAuthor Commented:
How can I run the macro in access? And easily copy the documents over
0
 
Martin LissRetired ProgrammerCommented:
Well it would help greatly if you could show the macro.

Without seeing the macro the first thing I'd suggest would be to add

Application.ScreenUpdating = False

at the start of the macro, and

Application.ScreenUpdating = True

at the end if you aren't already doing that.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
p_nutsCommented:
0
 
NorieCommented:
You might not need code if you had the data in Access, it could possibly be done with queries.

If you did need code it would be quite different from what you have now.
0
 
ahs_expertAuthor Commented:
Where in Access can I enter the macro? I cant find an editor (blank page) to write code like I can in Excel
0
 
NorieCommented:
Just like in Excel you can open the VBE using ALT+F11.
0
 
aikimarkCommented:
Don't use Excel for your database needs.
In the database world, what you are doing uses terms like table joining and queries instead of macros.
0
 
p_nutsCommented:
The trick in db country is to write functions that help querying.. Also you might need to upload the data then alter the columns with some queries..

Upload is easy there's a ton of import options in access ..

Can you share the column structure of the two tables and what your expected outcome is
0
 
Jeffrey CoachmanCommented:
ahs_expert,

The bottom line here is that with no data to examine, and no code to look at, ..it is impossible for us to suggest anything specific.

The other issue here is that this seems to be an *Excel* issue yet you also posted it to the Access zone.
Hence, you can't simple "convert" your Excel code to work in Access

Please just post a sampler of the spreadsheet with the code.
In other words, post something that we can open and see the issue.

- Back up your file
- Remove any records unless they are relevant to the issue.
- Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
- Unhide and hidden rows or columns
- Compile the code. (From the VBA code window, click: Debug-->Compile)
- Remove any Passwords and/or security.
- Post the explicit steps to replicate the issue.
- Test the file before posting.

In other words, ...post a file that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: 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!

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