Speed Up Code If Next Statement
May 12, 2006
I have developed (plagiarized) the following code but it is very slow. how I could speed this up? Eventually, the range may encompass more rows, and there are three separate ranges this code will act upon, so how to speed things up.
Sub DeleteBlanks1()
Dim i, Endrow As Long
Endrow = Range("K2000").End(xlUp).Row
Application. ScreenUpdating = False
For i = Endrow To 2 Step -1
If Cells(i, 11).Value = "" Then
Range(Cells(i, 8), Cells(i, 13)).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
View 7 Replies
ADVERTISEMENT
Aug 8, 2009
I'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...
View 9 Replies
View Related
Apr 10, 2012
My question is can I use cpu clock or some other means temporarily as an indicator before and after sections of code to display run speed until I find the best optimised code solution.
View 1 Replies
View Related
Feb 16, 2013
I have a very complex report with a variable numbers of rows in multiple sections. I am evaluating whether to display each row with a formula in the first column (those that I don't want to display have a "H" in the first column).
The following code works fine, but it takes forever. Ideally I wanted to trigger the code on the Worksheet_Activate() event but its so inefficient that I can't do that.
VB:
On Error Goto Errorhandling
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlManual
Application.StatusBar = "Processing..."
[Code] .....
View 4 Replies
View Related
Aug 30, 2006
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
Sheets("Calc sheet").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[1]C"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!RC[1]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]"
Range("C9").Select
Sheets("Schedule").Select
Range("G7").Select..................
View 3 Replies
View Related
Sep 13, 2006
I am trying to parse and remove unwanted rows from a very large text file using At the moment, the application runs rather slow and was wondering if the experts could give some pointers on how to make the code more efficient while still keeping it simple so others may be able to modify later. I am keeping the ScreenUpdating True as the alternative false will just show Excel as Not Responding to the user until the VB is finished.
Sub deleteReplaceRows()
Application.ScreenUpdating = True
Dim DeletedRows As Integer
Dim lastRow As Long
Dim Arr(7)
Arr(1) = "<PUZZLE>"
Arr(2) = "<%"
Arr(3) = "%>"
Arr(4) = "Response."
Arr(5) = "</PUZZLE>"
Arr(6) = "<HINT>"
Arr(7) = "<MESSAGE>"
For i = 1 To 7
Do
Set rng = Columns(1).Find(Arr(i))
If rng Is Nothing Then Exit Do
rng.EntireRow.Delete
DeletedRows = DeletedRows + 1...................
View 2 Replies
View Related
Mar 18, 2008
I have the following function in a worksheet module:
Private Function NixEmptyColumnsAndSuperfluousRows()
Me. Range("A1:A7").EntireRow.ClearContents
Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp
Me.Range("BE1").EntireColumn.ClearContents
Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AT1").EntireColumn.ClearContents
Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AK1").EntireColumn.ClearContents
Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("Y1").EntireColumn.ClearContents
Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("O1").EntireColumn.ClearContents
Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("K1").EntireColumn.ClearContents
Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
View 9 Replies
View Related
May 2, 2008
I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?
View 9 Replies
View Related
Mar 25, 2008
my 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()......................
View 7 Replies
View Related
Jul 21, 2009
I am trying to use the formula below in a macro to produce the same result, but I am having trouble with the coding.
View 4 Replies
View Related
Sep 27, 2009
On Sheet1 I have a macro link. On the Setup sheet, in cell C14, I have the name I want to display in the link on Sheet1. The wording is changeable in the Setup sheet. This all works fine.
Now, I want to modify it. I want an if statement at the beginning of the macro that says. If Setup, cell c14 is blank, I simply want the macro to stop and do nothing. If Setup sheet, cell c14 is not blank, I want the macro to continue as normal.
View 2 Replies
View Related
Dec 8, 2009
I am trying to make some additions to this code. Basically what it does is copy some data from another sheet. Now there are two types of copying here.
1. A ‘call value’ from another sheet is copied to this sheet.
2. The formula from the previous cell in the column is copied. (eg. Formula in C3 is copied to C4, formula in D3 is copied to D4, and so on)
So the macro works fine for all cases EXCEPT the initial entry, which will be in Row3, that’s where the formulae begin. So I need some kind of an If-Then statement.. to say if A3 is empty then do the following, If not then do something else....
View 12 Replies
View Related
Apr 29, 2014
Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.
Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.
If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...
My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.
What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.
Test.xlsm
View 3 Replies
View Related
Mar 27, 2014
I'm trying to convert my nested if statement below into vba code.
=IF(L27=15,"15+", IF(AND(L27>=5,L27
View 1 Replies
View Related
Aug 27, 2013
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
View 4 Replies
View Related
Oct 29, 2013
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
Here is my code
cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear
Set rs = New ADODB.Recordset
[Code] ........
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
View 1 Replies
View Related
Jun 18, 2009
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
View 3 Replies
View Related
Mar 7, 2014
I am trying to create the following function but I cannot seem to get it working correctly.
[Code] .....
View 3 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Feb 26, 2006
How to speed up Autofiltering? Excel 2003 sp2 hangs up for 10 minutes after
I choose a record in the Autofilter.
View 13 Replies
View Related
Dec 11, 2006
I'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?
View 9 Replies
View Related
Mar 4, 2010
I 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: ...
View 9 Replies
View Related
Jan 15, 2007
to speeding up this little routine that deletes rows that have identical values in certain cells in the row above
Sub remo() ...
View 3 Replies
View Related
Apr 27, 2007
I 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
View 5 Replies
View Related
Oct 11, 2013
I 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.
View 2 Replies
View Related
Nov 4, 2008
The 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 Related
Apr 21, 2009
My 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
View 5 Replies
View Related
Feb 17, 2013
I 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.
View 9 Replies
View Related
Aug 19, 2013
how to speed up a shared excel worksheet?
View 4 Replies
View Related
Mar 21, 2007
A1 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"....................
View 9 Replies
View Related
