Measuring Speed Of Code Sections?

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


ADVERTISEMENT

Standard Way To Organize Sections Of Code With Code Tags?

Jul 25, 2014

Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.

1. Dimensions and Variables

2. Data

3. Processing the Data

4. Rules of analysis

5. Analysis

6. Formatting

I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?

View 4 Replies View Related

Adding Code: Combine 2 Sections Of Code

Sep 9, 2009

I am now trying to combine 2 sections of code but being a complete VBA novice I'm stuck, where should the second Sub go so that it works,

View 4 Replies View Related

How To Speed Up Code

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

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 View Related

Speed Up Code To Programmatically Hide Rows?

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

Speed-Up/Optimize Recorded Macro Code

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

Speed Up Macro Code That Deletes Rows

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

Measuring Market Volatility

Jul 23, 2008

I have an LSE spreadsheet with a list of 150 stocks. I need to find the easiest way of calculating the volatility of each stock using either standard deviation or garman-klass. Is there way of coding it so i can probable pull through data from the internet nd run simultaneous calculation for each of the stocks?

View 9 Replies View Related

Speed-Up Multiple Row/Column Deletion Macro Code

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

Speed Up Macro Code To Delete Duplicate Rows

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

Measuring Text Widths In A Cell

Jul 20, 2006

Is there any way to measure the width of text in a cell in points? I know that the width of the cell is returned using the Width property.

I also know that the number of characters in the text can be returned using the LEN() function, but I don't believe the number of characters can be converted to points (because some characters are different widths, if I'm not mistaken)

View 9 Replies View Related

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, 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

Removing Sections Of Equation If Any Value = 0

Jan 13, 2009

The equation is basically: 10^[A/20]+10^[B/20]+10^[C/20]. Now imagine that there is no input value for C. I need to make it so that it won't add the bit I've highlighted in green.

View 5 Replies View Related

Sort Multiple Column Sections?

May 8, 2014

i have 7 columns from A to E of horse racing data.

Col A is Date, B is Time, C is number of horses in the race, D is the result placing E is the Odds

The data is in Date order and then by time then by order of place eg 1st 2nd 3rd etc

I am trying to sort the data by Odds for each race

Pseudo code would be;

Read Cell C2 (the start of the data) to determine the number of runners (example answer 10)
Select range Rows A2:A12 (10 rows from data in C2)
Sort Selection by column E[code]...

I wish the above pseudo code works as it seems so simple

View 2 Replies View Related

Formula To Count Based On Various Different Sections

Nov 10, 2009

I have a summary sheet with a table and it needs to show how many people have 14 or more hours available based on a few reports. This is split by different levels (i.e Manager, Senior Manager etc) and various different areas.

The reports are placed in seperate sheets and i need the table to be automatically updated via either a formula or macro, rather then the manual process of going through each sheet and having to filter and then count.

View 13 Replies View Related

Clearing Sections Of A User Form

May 22, 2009

I have a user form with a button to clear the entire form on a click. I also have the form seperated into 2 frames, how do I set a button to clear only one of the two frames. Also how do I set a button to clear one text box on a frame? I know it must be simple, but I can't seem to figure it out.

View 3 Replies View Related

Coloring Sections Of Pivot Table

Dec 21, 2011

CompanyCodePropertyCount of StegdiwSum of WidgetsCompany XYZ

Example 1XXXXXX10.00Example 1 Total10.00
Example 2ZZZXXX22.00XXXZZZZ22.00Example 2 Total44.00
Example 3QQQZZZ33.00ZZZQQQ33.00QQQXXX33.00Example 3 Total99.00COMPANY XYZ Total1413

I am recreating a pivot table made by a prior user using new data. I have been able to figure out the format. But I would also like to color the sections in the same way, similar to that show above. How to color different sections of a pivot table?

View 1 Replies View Related

Pie Chart With Two Sections - Red And Green Colours

Jul 2, 2012

I have a very simple pie chart, no 3D angles, no shading, no tilting, no exploding (i.e. exploded view).

In fact it is so simple, it is just 2 sections.

I want one section to be GREEN, and the other section to be RED

Green is good, Red is bad, of course, just like traffic lights.

So, how do I get these colours to be green/red. Not worried about which data series (of 2) is a particular colour, I can swop these easily.

View 2 Replies View Related

Deleting Duplicate Sections Of Data

Sep 15, 2012

In my spreadsheet (27,000 + rows), there are sections (multiple rows) that are duplicates of other sections. A section is a header row followed by other rows of data.

The number of rows in each in section can vary from 5-20 – therefore, you cannot use the number of rows as a criteria.

However, each section has a header row that contains a four digit number. The following is an example of two such sections:

Basic -- Zero Line ==> Zero Line ID=7076 (submitted by Calvin Campbell)

Star Thru

Pass Thru

Trade By

Star Thru

one half Square Thru

[Code] ..........

As you will notice the header row contains an ID number. Every header contains “ID=” followed by the four digit ID number.

I need a macro that will identify the duplicate IDs and their sections and delete the duplicate rows of data.

I mention the following – which may or may not be useful.
- Not only are the header rows duplicates but the content of the entire section is duplicate
- There are no blank rows in the spreadsheet
- Entire rows may be deleted (not just specific cells)
- The data will be in Column A
- There’s only one duplicate of any given ID

There are a total of 2716 sections. It has been determined that 201 of them are duplicates. I can provide the 201 duplicate ID’s if needed.

View 9 Replies View Related

Convert Formulas To Values On Multiple Sections?

Dec 3, 2013

I want to convert multiple sections that contains formulas to values. Usually i did this by coping range of cells, and paste as Values. But now i have multilple sections and excel doesn't allowe me to copy multiple sections. (i can not use clipboard, because there are more than 2.5 k rows)

View 7 Replies View Related

Excel 2010 :: Sorting Different Sections Of The Same Worksheet Differently?

Aug 14, 2013

I am using Excel 2010 64-bit (with SP2 and all other updates installed).

I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)

I need to sort this worksheet as follows:

1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.

Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.

Is there a way to combine these steps, perhaps with a macro?

I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.

View 2 Replies View Related

Copy Paste Certain Sections From Multiple Excel Files?

Aug 14, 2014

I have one master excel file (masterexcel.xlsx) and 100 small excel files. The small excel files are saved as M30.xlsx, M31.xlsx, M32.xlsx, M33.xlsx, M34.xlsx....

I want to open the M30 file, copy a certain section and paste it into the master excel file, close the M30 file whilst saving the new information in a file called recording. Then open the next file which is M31, copy a certain section and paste it into the master excel file, close the M31 file and then open m32 and so on....

How do I create a loop for the files so it automatically opens the files in the order M30, M31, M32.......and performs the aforementioned actions.

I have attempted the following for m30 but i need to make it a loop for m31, m32, m33, m34, 35 and so on....

[Code] .....

View 1 Replies View Related

Rank Records From Multiple Sections Of Single Array?

Jan 3, 2012

I am trying to rank records from multiple sections of a single array. Since there are over 100 sections I want to use a formula. Using the MATCH function I have the first row for each section of the array (Col D below). The psuedo code of what I'm trying to do is:

RANK(B1,Bfirst row of section:Bfirst row of next section -1)

Example (results in Column C):

A B C D
1 West 100 3 1
2 West 150 1 4
3 West 125 2
4 East 50 3
5 East 75 2
5 East 140 1

I will also need to increment the section as I complete each section of the array. Am I trying to do too much in a single cell??

View 1 Replies View Related

Excel 2003 :: Count Of Records Based On Criteria Within Different Sections Of Spreadsheet

Jul 5, 2014

I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.

How do I count the number of candidates in the spreadsheet?

In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.

There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.

I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.

(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)

View 4 Replies View Related

Excel 2007 :: Import Data From Worksheet Into Another And Group Data Into Sections

Mar 2, 2012

I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.

The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).

The following columns will be exported from Generated Report -

BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.

BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126

For Matrix

The data will be imported into Matrix worksheet into the following columns as part of the row headings

- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data

The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.

I want each section (marked by BRID number)to be separated by row headings.

These row headings are to be generated after populating all data within each BRID number.

When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.

The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath

BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do

BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do

So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.

View 5 Replies View Related

How To Speed Up Autofiltering

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

UDF Recalculation And Speed

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

How Can I Speed Up My Loop

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

Increase Speed

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







Copyrights 2005-15 www.BigResource.com, All rights reserved