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)
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 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 found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible. If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code: Sub RSLDASHBOARDV2() 'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D. 'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D. 'Do Not Modify Code Unless Given Proper Privileges to do so. Dim APPSPD As Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code: objField2.PivotItems( _ "TRC").Position = 1 objField.PivotItems( _ "MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code: Sub STATSPIVOT() 'STATS PAGE BASED ON STATS DATA TAB Sheets("STATS DATA").Select Dim objTable As PivotTable, objField As PivotField ActiveWorkbook.Sheets("STATS DATA").Select Range("A1").Select
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define: Select File Extension Select Folder to Search Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open() Sheets("Houselist").Activate Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("Front").Select Range("A1").Select End Sub
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open if workbook.name "inventorysummary.csv" then application.run "personal.xlsb!capacity" end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly. But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
