Speed Up Slow Macros?
Apr 4, 2007I use the Application.ScreenUpdating = False all the time. Any there any other things like this which speed up macros?
ADVERTISEMENT
Do LoopUntil Slow Speed
Mar 6, 2010I have written a macro which is sucessful in the sense that it does what I need. However, I ran it against my data for the first time today and it took forever. To give you an idea of the data size... my code had to loop through about 10,000 rows and move the ones that met the IF criteria to another sheet.
Can anyone provide any suggestions as to what might make my code faster? Should I take a different approach? I am definitly still wet behind the ears.
My code is below...
Dim count As Integer
count = 3
PeCount = 3
ActiveWorkbook.Worksheets("MyWorksheet").Activate
Do
If Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "1111111" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "2222222" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "3333333" Then
Sheets("MyWorksheet").Rows(count).Select
Selection.Cut
Sheets("YourWorksheet").Activate
Sheets("YourWorksheet").Rows(PeCount).Select
Selection.Insert Shift:=xlDown
Sheets("MyWorksheet").Activate
Sheets("MyWorksheet").Rows(count).Select
Selection.Delete Shift:=xlUp
PeCount = PeCount + 1
Else
count = count + 1
End If
Loop Until IsEmpty(Sheets("MyWorksheet").Cells(count, 2).Value)
End Sub
Speed Up Slow Copy Macro
Sep 24, 2007i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.
Sub autorec ()
Dim wSheet
Dim newSheetName As Variant Dim FNAC As Double
Dim OU As Double
Dim DS As Double
Dim CCY As String
Speed- How Do I Determine What Is Causing My Workbook To Be Slow
Apr 16, 2009I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
Sample Formulas: ...
Speed Up Search Application.filesearch Is Slow To Respond
Apr 4, 2007I am trying to determine in code whether a file exists in a certain location. This seems to be very slow even in a folder which contains just one file. Is there any way to speed up this process? Three seconds seems like a long time especially given that I will have to loop through this Sub many times. The files I am looking for are CSV so I can't use msoFileTypeExcelWorkbooks unless I can modify what file extensions this looks for. I only started dabbling with VBA a few weeks ago so it is entirely possible I'm barking up the wrong tree and should be using another method to acheive my aims.
Sub CISORTEST()
With Application.FileSearch
.NewSearch
.LookIn = "C:TEMP"
.SearchSubFolders = False
.Filename = "MYCSVFILENAME"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then
Call DoStuff()
End If
End With
End Sub...........................
Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same
Mar 25, 2008my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
Slow When Running Macros
Mar 21, 2008I have a simple Macro. I assigned to a Form button.
when i click that button systems takes lots of time to complete the macro.
left down task bar it say calcuating cells and load from 1% to 100% ...
Macros VERY Slow After Setting The PrintArea
Jul 3, 2008i have the following problem: as a Workbook_BeforePrint statement i wrote a macro in which i recalculate the print area of 2 tables in Sheet1 and Sheet2. The table autogenerates new lines through another macro and therefore i need to set the area because i cannot know from the start how big will be the table (how many lines it will have). I know only how many columns it will have (from cell B2 (or Sheet1.Cells(2,2)) to column 13, on Sheet1 and 5 on Sheet2).
As i said, before Print (or PrintPreview) excel triggers this Workbook_BeforePrint macro. In this macro i count the lines on a column which is the Current Nr. column, until cell is empty (=""). The macro works perfect. Here is the macro:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
setprint
End Sub
Sub setprint() .....
Macros Really Slow The Another Macros Is Run
Dec 14, 2009I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
Excel 2010 :: Legacy Macros Running Slow?
May 26, 2011I have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.
However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:
Code:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkBook.PrecisionAsDisplayed = False
It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!
Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't work and anyway I loose things, like data validation, when I save it in Excel 2010.
Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available
Aug 26, 2009I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedEnable/Disable Macros When No Macros Are Present
Jul 28, 2008I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
How To Speed Up Code
Aug 8, 2009I'm reading in a text file with stocks data in the following format:
JRV,Jervois SUM,D,20090807,000000,0.00600,0.00600,0.00500,0.00600,41370,0
JYC,Joyce SUM,D,19900102,000000,1.25410,1.25410,1.25410,1.25410,0,0
etc...
which then compares the three-letter code with a list in the following
format to create a new text file where data is only included if the code
is on the list. Since I added the range search to the code it runs VERY slow
AACAust A Foo
AAFAustral AfMat
AAMA1 MineralMat
AARAnglo AustMat
AAXAusenco LiCap
ABBAbb Grain Foo
ABCAdelaide BMat
etc...
How To Speed Up Autofiltering
Feb 26, 2006How to speed up Autofiltering? Excel 2003 sp2 hangs up for 10 minutes after
I choose a record in the Autofilter.
UDF Recalculation And Speed
Dec 11, 2006I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line
Application.Volatile
to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :
Sub DropDown4_Change()
Application.CalculateFull
End Sub
but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.
So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?
And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
How Can I Speed Up My Loop
Mar 4, 2010I will attach a sample of the data when I figure out how to. The original data takes about 8 or 9 second to delete the lines.
Code: ...
Increase Speed
Jan 15, 2007to speeding up this little routine that deletes rows that have identical values in certain cells in the row above
Sub remo() ...
Speed Up Loop
Apr 27, 2007I required to do some Monte Carlo analysis for 1000000 simulation. I have managed to find some free code, however, the time it took to run 1000000 >30min. Is that normal? The code that it took the longest to run is following:
For i = 1 To number_of_trials
Application.Calculate
For j = 1 To number_of_formulas
runs(j, i) = sel. Cells(1, 1 + j)
Next j
Next i
Is there any way I can implove this code to make it run faster? I have already tried Application. ScreenUpdating = False
Average Speed Of Competitors
Oct 11, 2013I have a time (1:08:31) that it took to travel 35km. How can I calculate the average speed of this competitors plus a number of others who recorded faster or slower time?
I managed to convert the time to seconds but when I load a simple formula to convert to KPH it never works.
Speed Up Clearcontents Macro
Nov 4, 2008The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.
View 5 Replies View RelatedSpeed/Efficiency For If-Else Construct
Apr 21, 2009My question is about the If-Else Construct.
I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.
My question is, how do you code "no action".
The following is what i usually
Trying To Calculate (Time To Top Speed)
Feb 17, 2013I have 3 numbers:
1) Current Speed
2) Current Acceleration
3) Acceleration Growth
Assuming:
Current Speed=0
Current Acceleration = 0.2 (each 'turn' the current speed will increase by this much)
Acceleration Growth = 0.2 (each 'turn', the current acceleration will grow by this much)
This gives a current speed over a series of 'turns' as
0.0 + 0.2 = 0.2
0.2 + 0.4 = 0.6
0.6 + 0.8 = 1.4
1.4 + 1.0 = 2.4
2.4 + 1.2 = 3.6
3.6 + 1.4 = 5.0
5.0 + 1.6 = 6.6
6.6 + 1.8 = 8.4
8.4 + 2.0 = 10.4
etc.
What I'd like to do is have a formula (or some way other than calculating each step) to tell me how many turns it would take for the Current Speed to =>X (example 100)
Basically, Turns to X speed = something clever * acceleration growth * something else very clever.
Speed Up Shared Workbook?
Aug 19, 2013how to speed up a shared excel worksheet?
View 4 Replies View RelatedPermutations Checkspelling Speed
Mar 21, 2007A1 has some characters
this code will generate all possible words, that can be made using all characters
system
permutate and checkspelling: if OK then write to column B
example
A1: iftrs
results: first frits rifts
Option Explicit
Dim CurrentRow
Const col = 2
Sub correctly_spelled_permutations()
Dim InString As String
Dim CalcSet As Integer
InString = Range("A1")
If Len(InString) < 2 Then Exit Sub
With Application
.ScreenUpdating = False
CalcSet = .Calculation
.Calculation = xlCalculationManual
.EnableCancelKey = xlErrorHandler
.StatusBar = "searching valid combination"....................
Speed Up Frame1.Clear
Aug 16, 2007I have a Frame on a UserForm. The Width of the frame depends on values given in TextBox.Dat1 and TextBox.Dat2.
Now I tried it with a width of 2200 with a scroll bar, but when I change the value in the Textboxes I need the Frame to be cleared. Now this is my question. I use "UserForm1.Frame1.Clear" but this can take up to 1 minute.
Is there anyway to speed this up?
Increase Speed And Simplify VBA
Oct 15, 2007I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula ....
How To Speed Up Select Case
Dec 2, 2007i hv a excel file which have 200k row. recently i learn from this forum using VBA (excel macro code) for select some data for analysis. it take very long. any suggestion will be apprecaited.
i already increase my note book ram to 2GB. it still slow.
Shortening A Speed Sheet
Apr 18, 2008weekly i have a report that is generated that is over 5000 lines and 4 columns wide.
I currently copy and paste one page column to make it 16 columns across the page. Is there a function in excel to do this?
Calculation/ Macro Speed
Sep 24, 2008I am having a big problem with recalculating cells after running a macro. The macro runs at a great speed, but once the calculation is switched back to automatic, the cells are not calculating fast enough. I waited for 4 minutes and it still had not moved past 0%. The worksheet has approximately 9000 rows out to IG columns. 85% of the cells contain an if-then formula.
View 9 Replies View RelatedHow To Convert/speed Up This Formula
Oct 31, 2008The formula: ={IF(AND(ISNUMBER($A3);($A3-DAY($A3)+1)=F$2);$D3;IF(AND(F$2 > ($B3-DAY($B3));F$2 < DATE(YEAR($C3);MONTH($C3)+1;0));$D3/DATEDIF($B3-DAY($B3);DATE(YEAR($C3);MONTH($C3)+1;1);"m");0))}
I need to use this formula for over more than 30.000 rows and more than 50 columns. Is it possible to speed up the formula? Or maybe to handle this by a macro?