Speeding Up Macro Process (Graph Algorithm Stuff)
Jul 7, 2009
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.
View 14 Replies
ADVERTISEMENT
Jan 15, 2007
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 ......................
View 9 Replies
View Related
Mar 13, 2008
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.
View 9 Replies
View Related
May 13, 2008
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)
View 9 Replies
View Related
Nov 25, 2013
I am using Excel 2007 and have a simple enough macro to paste data in to another sheet - simple is what I do best.
Sub Macro1()
'
' Macro1 Macro
With Sheets("data")
.Range("B3").PasteSpecial xlPasteAll
End With
End Sub
I am wanting to change this so that before it pastes any data into B3 it will delete all data from B3:AE22.
I have had a look around and found plenty to delete full rows or worksheets but not just to delete the data in those cells.
View 8 Replies
View Related
Jun 28, 2007
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.
View 9 Replies
View Related
Aug 23, 2007
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 ...
View 9 Replies
View Related
Aug 27, 2009
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
View 9 Replies
View Related
Jul 14, 2008
Delete rows where column A and B are duplicated.
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)).................
View 9 Replies
View Related
Mar 14, 2013
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?
View 1 Replies
View Related
Jun 30, 2006
I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)
For example:
Sub MyMacro1 ()
code
End Sub
Sub MyMacro2 ()
code
End Sub
If this process was logged, then the log file would look something like:
Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub
But it would be a in text just like a log file.
Is this possible with Excel ?
The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.
View 4 Replies
View Related
Aug 30, 2009
I wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
View 5 Replies
View Related
Jun 19, 2009
I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?
Range("A3:V3").Select
Selection.Cut
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("W2").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=-1
Rows("3:3").Select
Selection.Delete Shift:=xlUp
View 4 Replies
View Related
Oct 25, 2008
As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....
I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?
Or even tell me which sub routine it is up to? (but % would probably be better)
And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...
View 14 Replies
View Related
Oct 12, 2007
I am trying to speed up this macro, ive already tried turning screenupdating and calculation off, but it still takes forever, and I dont understand why.
The code is basically searching for a given string in column E. There are about 9000 lines to my sheet, which shouldnt take more than a 30 seconds to a minute to complete.
Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
For MY_ROWS = Range("E12000").End(xlUp).Row To 1 Step -1
If Range("E" & MY_ROWS).Value UserForm1.ComboBox1.Value Then
Range("A" & MY_ROWS & ":E" & MY_ROWS).Delete (xlUp)
End If
Next MY_ROWS
Also anyone know how to add another condition to make the macro stop the delete process at lets say Range("a1:e8")?
View 9 Replies
View Related
Dec 15, 2006
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
View 9 Replies
View Related
Feb 4, 2010
I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.
so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?
View 14 Replies
View Related
Jan 27, 2008
I am trying to write a macro that will run another macro (already written) through all the sheets/tabs in a workbook one sheet at a time and will stop once it reaches the last sheet/ tab.
Can you assist me in writing this macro.
View 9 Replies
View Related
Sep 12, 2007
I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??
View 3 Replies
View Related
Sep 7, 2012
Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB:
Sub openandcopy()
Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)
[Code] .....
View 4 Replies
View Related
Apr 2, 2007
I need to get this macro to process the cells for every worksheet in a book rather than just the active one
Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Mar 24, 2009
My problem is that data from previous applications are "bleeding" into new applications while running my macro.
I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.
I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.
I am using Excel 2003, SP2
Sub Process_Application()
'
' Process_Application Macro
' Macro recorded 3/23/2009
'
'
ActiveSheet.Paste
Columns("A:A").Select
View 9 Replies
View Related
Aug 1, 2014
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
View 7 Replies
View Related
Aug 8, 2007
I have a script that i want to run for each row on my worksheet, but don't know the correct way to do a foreach using VBA.
For Each row ...
Do this code
... end for each
View 9 Replies
View Related
Jun 1, 2009
my worry now is to protect my program from being played with as a lot of people ,some not so bright ,have access to my sheet can i protect my sheet and still have the macros function.
View 9 Replies
View Related
Dec 14, 2011
Sometimes I need to copy something to the clipboard, then go to another sheet (or somewhere). When I get "there", I sometimes want to insert rows, without "inserting cut/copied cells" . . . . So I end up saving the worksheet to clear the clipboard, then inserting my rows.
Can I insert blank rows while maintaining my clipboard?
View 3 Replies
View Related
Feb 19, 2014
I have a list of model numbers that corresponds to part numbers i.e:
model number
part number
abc
123
def
456
[code]....
I can type in A2 = ab and it autofills the model number abc, and have B2 automatically have the part number which is 123 filled in as well.
View 4 Replies
View Related
Dec 18, 2007
I would like to create a single excel file, which is composed by fragments of different (closed) excel fies (it should take some data from the files).
View 10 Replies
View Related
Nov 4, 2008
I have a Weight Watcher spreadsheet. I enter the Calories, Fat and Fiber and I get the # of points for that food. Then I keep put those points into the lower part of the spreadsheet where I track what I eat, and Excel does my simple math for me. What I want to do is keep a list of my foods, and their point value in my spreadsheet. I would like to be able to put the food name in Cell A1, the WW points in BI, and then be able to click a button with a macro that takes those two pieces of data and add them to bottom of my food list. I don't have a food list yet, I would assume I can just put it on sheet 2. I then want to be able to use lists in my cells to choose the food in the future. I need to be reminded how to make "dynamic named ranges. Today, I just need to know how to write the macro to copy and paste those two cells in the first open row at the bottom of my food list.
View 9 Replies
View Related
Apr 29, 2012
When I view my page on print preview it shows 2 pages but I only have stuff on one page the second page shows 2 border marks but when I go to erase them I can't find them. Is there some way that I can find them other than look at page after blank page.
View 4 Replies
View Related
