Vba line of code takes a long time to run

[RogerRabbit1212]

Hi all,

New to the forum but wondering if anyone could help. I have inherited a vba database in excel with code i'm trying to edit without needing to rewrite.

The purpose of the database is to house transactions for customers. Users update templates which get pulled into a log sort and formatted and then update to the database which holds single/unique records with history.

It has quite alot of modules but one in particular runs extremely slow. This entire module deals with loading the templates into the log. Stepping through the code i can see that the below section of code is the reason. This problematic bit of code comes after the data in the templates are collated into one temporary 'holding file' and formatted. Everything before and after here takes seconds this section however can take upto hours depending kn the number of rows of data.

(1) What is it doing?
(2) Can i rewrite to do the same/similar thing but faster

I've tried just removing it but it has an impact on the overall sequence of code.

/////Problematic bit of code takes a long time run depending on number of records:

change_data is long
IN_UPDATE is a column name "update"

For change_data = 1 To range_in.Rows.Count
range_in.Cells(change_data, IN_UPDATE).Value = Trim(CStr(rg_in.Cells(change_data, IN_UPDATE).Value))
Next change_data

 

[Marc L]

Hi !​

I have inherited a vba database in excel

This is the worst idea to use Excel as a database as any database software is at least 50 times faster so yes Excel can be slow !​

​

one in particular runs extremely slow

The worst idea in your poor piece of code (as my comment may vary with the complete code) is to write cell by cell rather than all cells at once !​

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Vba line of code takes a long time to run
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RogerRabbit1212]

My sincerest apologies for cross posting. Had i read the rules properly would've known the correct comduct. How do i go about deleting my post please?

 

[Fluff]

No need to delete the thread. If you have asked this question anywhere other than here & Chandoo, please supply the link(s). Thanks

 

[RogerRabbit1212]

Vba line of code takes a long time to run - OzGrid Free Excel/VBA Help Forum

[xpost] [/xpost] Hi all, New to the forum but wondering if anyone could help. I have inherited a vba database in excel with code i'm trying to edit without needing to rewrite. The purpose of the database is to house transactions for customers. Users…

www.ozgrid.com

 

[Fluff]

Have you tried Rory's suggestion on Ozgrid?

 

[RogerRabbit1212]

Have you tried Rory's suggestion on Ozgrid?

I have tried it yes and that has made a difference currently reading the rules. Will reply shortly as solved ??