Vba line of code takes a long time to run

RogerRabbit1212

New Member
Joined
May 9, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 9, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
Have you tried Rory's suggestion on Ozgrid?
 

Forum statistics

Threads
1,136,952
Messages
5,678,745
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top