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")?
I have the following Input Boxes. If cancel is pressed on any box then I want the process stopped. eg if I enter an answer on sya questions one or two then click Cancel on question 3 I want the process stopped.
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
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.
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.
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?
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.
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?
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.
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??
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)
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 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
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 '
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
i need to make a button that Immidiately stops all macro's, Or (and This will maby be little more difficult) Stop all macros on a specific time inserted in 1 cell
the macro's that i want to stop running, are all single macro's which i activate with only 1 button i created.
i like something like this:
if "A1"(this is the cell where i have my updated clock) == A2 (the time i insert here stops the macro's at this time) do stop Macro's
If you run it now, ctrl t, it produces letters and changes alphabets as it hits a yellow box. If i change or add a yellow box, the letters change fine. The macro works perfectly, but instead of inputting the range or K3 to what ever, i would like to to run this macro and stop once it sees the row with the sort number of 4. ( this changes row postion depending on which sheet im working on, this is the problem).
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
I have a spreadsheet containing a macro to automatically sum values from week to week and display the max/min and average. This is almost fully working. The problem I have is the macro copies the sum formula down the page and doesn't stop at the last row. It always adds one or two extra rows. The formula should stop at row 77 however this does not happen and therefore the sum keeps going. Which in turn, distorts the max/min figures.
I'm trying to create a macro to input information into a cell then repeat until the information stops.
So say I have 10 rows of information that fill up A1:D10. in E1:E10 I'd like a macro to insert some data into E1 then go to E2 and do the same until the end and then stop. So since A11:D11 would be blank the formula would just stop.
I actually work on 100's of cells a day and this is for a bigger project I'm trying to put together or I would just drag.
I have got he below code in "thisworkbook" it works fine but I have a problem with it if it has been closed before the time expires. If it is closed it tries to reopen it and then close it again. So for my users who have got their security set to prompts the file will be closed and a message box appears saying "enable or disable Macros". If it is already closed than i dont really want this to happen. My security is set to low but i still see it flash up and the bottom then dissappear again. How can i get the Macro to stop if it has already beed closed??
Private Sub Workbook_Open() Sheets("database").Select Range("au16").Select Application.OnTime Now + TimeValue("00:15:00"), "TimeOut" If ThisWorkbook.ReadOnly Then MsgBox ("The file is currently being used by another user, please contact them to ensure they have not left the file open.") ActiveWorkbook.Close SaveChanges:=False End If End Sub
Sub TimeOut() ThisWorkbook.Close SaveChanges:=True End Sub
I am running a macro that populates the same chart with variable data. I have approximately 40 sets of data and wish to pause or stop the macro to view one of the sets of data more closely. The pause option would be better but Stop option is acceptable
