I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.
This may be a long shot, but I've been working on a macro today which runs a few Vlookups, does some copying and pasting and some re-aligning....basically nothing too complicated. The Vlookup is looking through approx 45,000 records and returning about 200 - 300 on average, this info returned is company name, address, contact info etc etc.
This macro is taking a ludicrously long time to run, I've checked through and removed any coding that I think is unnecessary without altering the final outcome of the macro.
It still takes ages. Has anyone got any ideas why this may be, or any tips of speeding it up?
I have a brand new computer, dual core processor, 2 gigs of ram etc so it shouldn't be performance of the machine slowing it down, and I ran it with the least applications open that I can (I'm at work so can't close everything, but none of the other applications are memory hogs or anything). I'm running on Office 2003 on Windows XP.
I am running a rather large simmulation where I have 12 for loops with each running between 0 and 100% with variable steps and the where the sum of the 12 variables has to sum to 100% and sometimes combinations of the sums of a few of the 12 have to sum up to specific values. That part works, now I am trying to get this to run faster.
Question: Is there a different design that might be able to run this process more efficiently? If not, is there a way to speed up the process by letting the computer know that all these variables are between 0 and 1 (or 100%) and that I only need 3 digits after the decimal if the value is in % form?
(the only problem with the rounding or cutting off the decimals at the end that I can think of is that it will only speed up the process a bit but will also make the summing upto 100% or any other value difficult)
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
What am I doing with For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...
However, this runs extremely slow. I had to stop it after about 10 min, as it only got to about row 1000 of 20,000.
Sub Delete_Dupes() Dim rw1 As Long: rw1 = 1 Dim rwx As Long: rwx = rw1 Dim stepx As Integer Dim co1 As Integer: co1 = 1 Dim co2 As Integer: co2 = 2 Dim bool1 As Boolean Dim bool2 As Boolean Dim bool As Boolean Dim count As Integer count = 0 Do Until Cells(rwx, co1) = "" stepx = 1 If rwx > rw1 Then On Error GoTo NewCrit bool1 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co1), Range(Cells(rw1, co1), Cells(rwx - 1, co1)), 0)) bool2 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co2), Range(Cells(rw1, co2), Cells(rwx - 1, co2)), 0)).................
1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?
2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.
The variable website is determined by earlier parts of the code that direct the macro to a specific network website. Sub Half_Hour_Data
1 'Open Internet Window and Navigate to Website Set myIE = CreateObject("InternetExplorer.Application") myIE.Navigate Website myIE.Visible = True Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads
[code]....
Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?
i'm working on floyd algorithm macro for a project, problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet And this is taking wayyy too long, i've been waiting for almost 2 days already.. here's the file : http://ifile.it/6v2j39f (You start the macro using "run" command in "input" sheet)
So is there any way to speed it up? Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm, http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result, and that's for 1 iteration, can't imagine how long it takes for 1043 iterations You can do this by deleting the.
I've been working on the following code and need to find a way to speed it up.
The premise is that I have a list of columns (From C2 to potentially EH and beyond) and these columns will contain the values Y, N, end or nothing. I'm looking for the quickest way to zap along the columns and delete all that have an N and to stop when end or "" is reached.
There are 2 versions as I'm not sure which one is best ...
Range("B2").Select LastColumn = Range("A1").End(xlToRight).Offset(, 1).Column For k = 2 To LastColumn ActiveCell.Offset(0, 1).Select ' right 1 If ActiveCell.Value = "Y" Then Next k End If If ActiveCell.Value = "N" Then ActiveCell.EntireColumn.Delete 'If employee has no sickness then delete column ActiveCell.Offset(0, -1).Select End If If ActiveCell.Value = "end" Then Exit For If ActiveCell.Value = "" Then Exit For Next k or a longer version
StripSickness: Range("B2").Select ActiveCell.Offset(0, 1).Select ' right 1 PrintView = ActiveCell.Value 'Y/N whether employee has data If PrintView = "Y" Then GoTo StripSickness If PrintView = "N" Then GoTo NotNeeded If PrintView = "End" Then GoTo Complete 'last column shows "end" If PrintView = "" Then GoTo Complete
NotNeeded: ActiveCell.EntireColumn.Delete ActiveCell.Offset(0, -1).Select GoTo StripSickness 'once employee is deleted, go back to stripsickness process
I've been working on cleaning up some code that copies particular information from one sheet (as the original must remain unedited) and then edits out irrelivant information.
However I've noticed that my "before" code moves far faster than my "after" code, which is odd considering that I've cleaned out a lot of excess code and the end results are the same.
I attach an example of my old fast code and my new slow code - can anyone advise where I have gone wrong ? Run the fast code first and then the slow one - you'll see the difference!
first, id like to thank the board for the help on setting up an array or ranges.
ive been able to get my code to work but its painfully slow...if anyone had any recommendations on speeding it up would appreciate it...i was playing around with using formulaarray instead of writing and reading the data on each loop but cant get it to work...and i'm not really even sure thats the right way to go.
i copied the entire code below...what i was doing was taking 8 time series and looping thru each one to calculate some basic statistics...i ran the function EDFlyRateFromPrice to calculate another series of data which i then run the standardize, max and min functions on.
I am putting together a workbook which calculates a rota for 5 days that shows overall headcounts every 15mins. It currently has 12672 cells containing SUMIFS.
But this takes a very long time to calculate.
Is there a faster / more efficient way for me to approach this task?
Can I attach the workbook so you can see what I mean?
Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.
When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down.
Concatenate Private Sub:
Option Explicit
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub ......................
I have a macro (see attached) that works fairly well, but the one thing I cannot figure out how to do it how to merge cells whenever there is a break in Project ID.
If you take a look at the spreadsheet, you will see that columns B and C for the 2nd Project ID is merged just how I want it to look. The number of rows in each Project ID is variable.
1. Macro runs and Insert 10 blank rows after each item row. EXCEPTION: if rows are identical on Column A do not insert. Insert - only after non-duplicates. There are times where there 3-4 rows that are part of the same group.
2. Insert a bottom border line to separate each group. See attached for sample.
I have an array which includes an if state as such:
for j = 0 to 5 for i = 0 to 10 if menu.value = "Class 1" then Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = Class1_Schedule(j, i) elseif menu.value = "Class 2" then Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = Class2_Schedule(j, i) else Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = NightClass_Schedule(j, i) next i next j
I want to write some sort of formatting code within this forloop to say if menu.value = "Class 1" then position (j,1) = a number to 0 decimal places and position (j,2) has data validation lists.
I know how to use the record macro button but I dont want to code for fixed cells. I would like to use the forloop to do it.
I am looking to create a macro that conditionally formats a cell depening on what words it contains.
if the cell has the word "level 1" in it = pink backround "level 2" = red backround "level 3" = Orange backround "level 4" = Green Backround "level 5" = Blue back round
What I want to do is format A1 by either a fill colour or text colour. Any type of format I guess. If A2 is "Yes" then A1 is green and If A2 is "No" then A1 is red.
To start off macro should pick selected columns such as owners and their projects, start date, price Final price, priority and status columns and paste into new sheet
At the same time only pick selected owners(Dave, Brian, Ken, Russell, Ben)data along with their high low, and medium projects then format the price and the final price column (that is to first convert them to numbers, then change it to the currency format and set the decimal place to 0) and then sort owners (AtoZ) along with Price and final price columns (largest to smallest) into a new workbook and to a new sheet in the same workbook with a name to the sheet called Extracted data
note owners may change and new members might get included in the list ( I am not sure if something dynamically can be done about this in the macro)
I have included sample data and how the output should look like.
I have a complex sheet where rows are continuosly overwritten and new data needs to be formatted each time. My macro works great aside from the fact the conditional format formula expression adds double quotes rendering the conditional format useless. After running the macro I can go into the conditional format field, remove the quotes, and the formatting formula works as expected. So...I first did a "record macro" to grab the code for conditional formatting:
I am having trouble with what I thought was a simple macro.
Data from report comes in dd.mm.yyyy format so I need to replace . with /
When manually carrying out the replace operation it works fine but when recorded macro carries out the operation a further task does not go as expected.
Next column is AGE (=today()-[date]+1) gives some unexpected results i.e. incorrect to the point of 100's days out!
I have a very large spreadsheet that is run by a different department. Regularly, I need to go into it do the following:
-filter the sheet by the information in one particular column - - the column is always the same but the number within it by which I filter changes as needed -double-click each column to autofit the columns -go to the page break preview to make the document fit one page -print the results.
My question is can I automate this somehow with a macro or something else?
I have to create an identical button on 20 sheets.
I have a shape creation macro but it creates in the default format.
Can I either change the default format so they are created in the way I want or code to add formatting? I need no shadow, can colour (pref with gradient fill), bevelled edges and text.
Sounds like the first option will be easiest if it works!
I've made a push button on the sheet. By selecting a couple of cells and then pressing this button I want to change the background color of the selected cells as well as add some text (same for all selected cells).
