Test Speed Of Macro On Different Systems
May 26, 2007ran the following macro on the new computer and on two other computers...
View 9 Replies
ADVERTISEMENT
2000 And Operating Systems
Aug 23, 2009If I have an application developed in Excel 2000 with Vista Operating System, does it matter what operating system (XP or later) under which it runs.
View 2 Replies View RelatedTest Two Cells And Set A Third Cell With Different Values Depending On Test Results
Aug 21, 2013I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
Calculating Time All (redundant) Systems Down
Sep 20, 2013I have redundant system groups. One group may have (for example) two computers running. Another group may have eight computers running. But within any group only one computer needs to be running for the system to be operational.
I have a row with down start date/time (Column-A) and down end date/time (Column-B) for each downtime event. In then next columns I have the start times and end times for the other computer(s) in the group.
Finally, I have a date column to the right of all others (one row per day). Next to the date column I need to have the value that represents the common time all computers in that group were down.
I don't much care if this is done with a formula or by VBA. I can split the date and time values into separate columns and format the values however needed to facilitate the calculation.
I've placed an example workbook at the following location. [URL] ...
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 RelatedCalculation/ 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 RelatedCan I Speed Up The Macro By NOT Saving
Sep 4, 2009I have this analyze that is runned by a macro in one workbook, and it starts a analyzing-process in another workbook. The data is picked up in the no2 book, and returned to the first book. It is analyzing lots of workbooks, sometimes up to 1000 workbooks, it means that no2 workbook gets a new name and then saved.
I have once heard that the process could be way faster if the workbooks where the analyzes is processed through not were saved, and I actually dont need the books as long as I got the data into my first workbook.
But Im not sure what in the macro that makes it save the no2 workbook, but I would really like to speed up this process. As it is now I have to start the analyze before I go to bed, and the hopefully it's done when I wake up next morning.
How To Improve The Speed Of A Macro
Mar 2, 2010I wrote a macro that works fine, although it runs extremely slowly as if plodding along through all of the cells one at a time. I'm sure the computer is faster than that, so I would like it to chug through more quickly.
I am using some syntax that could be optimized ....
Record Speed Of Macro
Dec 14, 2006I'm trying to determine the speed of a macro. I searched and have had no luck. recently with some help I reduced my macro speed from minutes to seconds and I was wondering is there code out there that I can record the speed of an existing macro.
View 5 Replies View RelatedSpeed Up Macro Deleting Every Nth Line
Jul 4, 2012I am dealing with data sets from various instruments that have different sample rates. I am deleting data points I don't need from some of the sets with higher sample rates so that all the data is on the same time scale.
The macro I have is super simple, but incredibly slow. I'm simply deleting every other cell down a column.
VB:
Sub OATcondense()
Application.ScreenUpdating = False
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
Any Way To Speed Up A Hide Rows Macro?
Sep 12, 2009Does anyone know a more efficient way to run this code?
View 7 Replies View RelatedSpeed Up A Macro (email & Printing)
Jan 11, 2010I've got a rather involved macro that's running kind of slowly, and I would appreciate any help I can get speeding it up. It's in two parts; the first is to create and email a report, the second is to format so it's pretty for printing. The full codes for both routines is pasted below.
The email part I developed first and it runs pretty quickly. Afterwards, I added the second macro, which is called halfway through the first.
Stepping through the code in the second macro, the problem I see is in this section, the setup for setting the heighth of merged cells in the report:
Macro Performance - Speed Tuning
Jun 4, 2008I run a simple macro loop to clean some data across nine columns. The purpose is to collapse the data in the columns so that column 1 has the first value found in that row, for the set of columns. For instance, if columns 1-4 are empty, it deletes / shifts everything left until the first column is not empty. Then it goes to the next row and repeats. Data can range from a few rows up to 6000.
[I have a period in the data as the cell content to evaluate]
Sub A_Rollup_collapse()
StartT = Now
Dim Col As Integer
Col = Range("IV1").End(xlToLeft).Column - 9
LastR = Range("A60000").End(xlUp).Row
Application.ScreenUpdating = False
For R = 2 To LastR
Do While Cells(R, Col) = "."
Cells(R, Col).Delete Shift:=xlShiftToLeft
Loop
Next R
EndT = Now
Application.ScreenUpdating = True
msg = "Done. Duration: " & Format(EndT - StartT, "h:mm:ss")
MsgBox (msg)
this macro (B) runs after another macro (A) that populates the nine columns with data using vlookups. Macro (A) It builds out a chain of information from col 2 to col 9, converts to values etc. Nothing odd.
When macro (B) is called right after running macro (A), it can take about one minute for 500 rows of data.
When I save and close the workbook, reopen it and run macro (B), it only takes one second.
When I insert a ThisWorkbook.Save between the two call statments, macro (B) still takes over a minute.
A minute is not too bad but when I'm dealing with thousands of rows, the difference is more like 30 seconds vs. 9 minutes which is a problem.
Speed Up Macro: Deletes All Duplicates
Jun 6, 2007It takes all numbers in column 4 starting with Row 15 and deletes all duplicates. It then shows the number of times the number was duplicated and puts this number in column 3. MY PROBLEM: The macro searches each line and takes FOREVER! I have data with thousands of lines. I already tried the screenupdating method which really doesn't help that much. Is there possibly a better code for doing this?
Sub Factor()
Dim sID As String
Dim sOldID As String
Dim lLastRow As Long
Dim lrow As Long
Dim lcount As Long
Dim lLoop As Long
lLastRow = ActiveSheet. Cells(Rows.Count, 1).End(xlUp).Row
lrow = 15
sID = ActiveSheet.Cells(lrow, 4).Value
sOldID = "ActiveSheet.Cells(4, 15).Value"
lcount = 1
lLoop = 1
Do While Len(sID) <> 0
If sID <> sOldID Then
If lLoop = 1 Then.................................
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
Attempting To Speed Up The Time It Takes The Macro To Run?
Jan 8, 2013I have recorded 7 different macros and then combined them all into one macro to achieve one end result. I am not sure if you can just look at the codes to determine different ways to improve them or if you need the excel spreadsheet as well.
View 2 Replies View RelatedSpeed Up A Macro (text File Import)
Jan 13, 2010Attached is a workbook that takes an imported text file, inserts rows with text.
It is SLOW, you can watch each line being inserted with the text, I would think with such a small sample dataset it would be much much quicker.
Information: See attached xls file.
On sheet1 is the imported data, sheet2 is a copy of the imported data so one can copy and paste to sheet1 as needed with out re-importing for test purposes and only for this query. (Sheets2 thru 3 are not used otherwise).
There is a command button on sheet one which will run macro "aaa". If you run this you will see how slow it is and exactly what it is doing.
I am simply looking for a way to speed this up, I have some files that are 10 times the size of the sample data and they take 10 or more minutes to run.
Speed-Up/Optimize Recorded Macro Code
Aug 30, 2006Im 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..................
Speed Up Macro Code That Deletes Rows
Sep 13, 2006I 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...................
VBA - Change Calculation Settings Of Workbook To Speed Up Macro
Sep 29, 2011I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
Speed Up Macro That Adjusts Row Height & Column Width
Oct 30, 2009This used to (and on some machines still does) work very well, but all of the sudden on my work machine, it is incredably SLOW!! The whole program takes a list of tasks and dates, and creates a wall calendar from them. Then (the offending part, below in code) looks at each cell on the wall calendar and formats it by sizing the height of a merged cell so that the entire task is displayed, then goes on to put a square around it (works fine).
The formating of the cell height/width can take up to 11 SECONDS each cell. What is happening to do this? Again, it USED to, and on some machines still does, FLY through this process. But now it just crawls. Code below, with the 3 offending lines identified.
Private Sub Fit_Height(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Application. ScreenUpdating = False
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
An additional item (clue?) is that, after the macro is done running, it is still unbearably slow to do anything on-screen. Grabbing the column header and changing the column width takes 5 seconds or so before it snaps to the desired width and displays correctly. Closing Excel and reopening it takes care of that problem.
More advancement (as I wait). It seems to be the Application.PageSetup that kills it. As soon as I hit the first Application.PageSetup line, the app grinds to a halt, and almost every line after that that manipulates the screen format or the page format takes 5 or so seconds EACH to process. I put them all at the end, but that really doesn't solve the problem, nor explain it, nor give me a true solution.
Speed-Up Multiple Row/Column Deletion Macro Code
Mar 18, 2008I 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..............
Speed Up Macro Code To Delete Duplicate Rows
May 2, 2008I 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 RelatedMacro To Test For Empty Worksheet?
Feb 14, 2012Is there a macro command that I can use that will tell me if a worksheet is complely empty?
View 2 Replies View RelatedMacro To Work Between My TEST.xls File And Another Worksheet
Feb 19, 2009Below is the macro to work between my TEST.xls file and another worksheet which is opened within the same workbook with the TEST file. If there are more than 2 files, the code ActiveWindow.ActivateNext won't work.
The number of files received from my suppliers varies depending on how busy the volume is. It can be as many as 20 files a day.
ActiveWindow.ActivateNext
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[5]
Speed Improvement - Macro Extract Higher/lower Values And Note Time Differences
Jul 8, 2009I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR
Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
Increase Speed Of Macro: Match In Font Color It Will Fill The Top With Orange And Delete The Bottom
Dec 12, 2006My macro works fine but I'm interested in seeing if I can speed it up. What I'm doing is starting at the bottom and comparing it with the row above and if they match in font color it will fill the top with orange and delete the bottom. This works but usually takes several minutes.
Public Sub ADMINCompareList()
Dim varTest1, varTest2
Dim lng As Long, i As Integer, iTest As Integer
Application. ScreenUpdating = False
Worksheets("ADMIN").Activate
For lng = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Not Range("M" & lng).Font.Color <> Range("M" & lng - 1).Font.Color Then
Goto newrow
End If
varTest1 = Intersect(Range("J:W"), Rows(lng))
varTest2 = Intersect(Range("J:W"), Rows(lng - 1))
For i = 1 To 14....................................
Test/Compare Pivot Table Fields Macro Code
Apr 24, 2008I wrote a macro for a pivot table.
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Export!R1C1:R3000C53").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Reference"), "Count of Reference", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("ACN received date")
.Orientation = xlRowField
.Position = 1
End With
What i want to know is if there is a code line to test if "Non Processed" value exists in column "Status" and then hide the value.
Now, if the macro does not find one value just gives me error.
Ship Date Based On Current Systems Date
Dec 14, 2011I have a report that in column A shows a ship date. These ship dates may be "Previous","Same Day", "Next Day", or "Future".
I need VBA code that will look at the day in column A and determine if it matches the current date on the computer if it does then in column W in the same row I need the string "Same Day".
If its the systems date plus 1 day column W in the same row would state "Next day".
If its the systems date plus 2 or more column W in the same row would state "Future".
If its any date prior than than the current system date then column W in the same row would state "Previous".
Macros Crahes - Alert Alert - Shut Down All Systems
Mar 27, 2007Each month I get a report that I process using a macro. The problem is that each month the name of the file changes and is different. When I run the macro and it crashes I have to de-bug by going through the code to change all references to the file name from the the previos run and change to the latest file name and then re-run the macro. There must be a better way.
I would like to learn the code that sees the open book and then refers to it for the run.
What is the best way to do this?