• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba line of code takes a long time to run

Status
Not open for further replies.

RogerRabbit2323

New Member
Hi,

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"

>>> use code - tags <<<
Code:
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
 
Last edited by a moderator:

vletm

Excel Ninja
RogerRabbit2323
This problematic bit of code comes after the data in the templates are collated into one temporary 'holding file' and formatted.
Could You do 'slow task' while You collect data?
Why do You use variable with column name "update" ... if it is fixed?

Few things could do before Your code as well as after Your code.
You should able to upload more code with Excel sample file to get better image ... what could do?
 

RogerRabbit2323

New Member
RogerRabbit2323
This problematic bit of code comes after the data in the templates are collated into one temporary 'holding file' and formatted.
Could You do 'slow task' while You collect data?
Why do You use variable with column name "update" ... if it is fixed?

Few things could do before Your code as well as after Your code.
You should able to upload more code with Excel sample file to get better image ... what could do?

Hi Vletm, thanks tor your reply. May be due to my beginners knowledge, what is a slow task?

I have attached a word.doc with abit more of the code if this is permitted. I have to admit my skills are not developer esq so apologies if this is messy or unlegible intentions are there to improve quality, like i mentioned much of this is inherited.
 

Attachments

vletm

Excel Ninja
RogerRabbit2323
You sent three lines of Your code, which You wrote is slow.
I asked ... I tried to ask Excel sample file ... Your doc-file is something different which I asked.
... and it's more challenge to test - verify - how to make Your code 'faster'?
 

Marc L

Excel Ninja
This thread is a wild cross posting so as a reminder :​
 

RogerRabbit2323

New Member
Hi, my sincerest apologies for rule breaking do not wish to waste anyone's time and help and comments greatly appreciated.

Will read carefully the rules and resubmit appropriately
 

RogerRabbit2323

New Member
This thread is a wild cross posting so as a reminder :​
Apologies for rule breaking. Can i remove so this is only active via one forum?
 
Status
Not open for further replies.
Top