Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.
I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).
But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.
- I would say I have about 2000 rows, and 15 columns. - They have sumifs formulas. - They link to a different workbook. - The workbook I am working on saves to the network - the source of my sumifs are also in the same folder on the network - the recalculation takes about 10 seconds at most - i have turned off recalculate before saving, it is all on manual calcs
- when i hit save, there are no calcs being performed - there are no macros in the workbook - there are only about 2 names in the name manager - then it freezes for about 10 minutes. - then the progress bar starts moving then it saves.
What is it doing in those 10 minutes?
1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.
Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "N:N" Dim Cmnt On Error Goto ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Row > 3 Then If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0 End If If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39 End If If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6 End If.....................................
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 recorded 7 different macros and then combined them all into one macro to achieve one end result. I am not sure if you can just look at the codes to determine different ways to improve them or if you need the excel spreadsheet as well.
I've done quite a bit of searching in the forum and online and haven't found anything that's generic and can be used at anytime.
What I'm looking for is a way or for code that tells you how long it takes a macro to run from start to finish, something that can be used to time any macro. I've seen some threads in the forum where people indicate that it took x amount of seconds for their macro to run but not sure how to do it.
I am trying to create a macro that can take the average of the the first 24 cells within a sheet, place the answer onto a cell in the next sheet (e.g. sheet2 in cell A1), then go back to the previous sheet, take the average of the next 24 cells within the sheet and paste the average of this new set in A2. I want to create a loop that will do this 365 times.
I have only managed to create the following code, however its only obtainning the average for the first set of 24 cells starting from B6 in sheet 1. I dont know how to use offsets that well....
VB: Sub Oval1_Click() For i = 1 To 365 Sheets("H1 - Riser Turret pressure").Select Range("B4").Select ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!RC:RC)" Range("B4").Offset(1, 0).Select Next i End Sub
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.
Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.
Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.
i need to make a macro that takes workbooks or files and runs them through another macro. i already have the 2nd macro done and it is working perfectly i just need to know how to make the one that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.
to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).
To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.
I'd like to have a UDF so I can just enter the formula:
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39". Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
I have a large formula written in my VBA code (a sumproduct with numerous variables) which exceeds the line length in VBA and therefore a proportion of the code is shown on the next line.
However, the VBA treats this second line as an error (since it sees it as a new line of code which on its own doesnt work) What do I need to do so that it treats the two lines as a single line of code?
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:
We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
I have the VB Editor open and am manually activating different workbooks in Excel (with 20/25 modules each), the VB Editor goes through a process of maximising each and every module in the workbook I have selected before I can edit any code or, indeed, do anything in the workbook. This process takes about 8-10 seconds every time I select a different workbook in excel! I have played around with the settings "Full Module View" in the options section of the VB editor, but to no effect.
I've written a piece of code that is so long, I have to use the scroll bar to see the whole of it, which isn't very user friendly
if there's a way of splitting long lines of code over say 2 or 3 lines, so I can read the whole thing without having to use the scroll bar? I've noticed some people use _ at the end of the code and then continue writing on the next line, but when I do this, I get an error message saying
"Compile Error : Expected : line number or label or statement or end of statement"
I have a fairly simple macro that takes a few seconds on my XP-computer with Excel 2003 but takes several minutes on my Vista-computer with Excel 2007.
The XP-PC has 2GB memory, the Vista-PC only 1GB, but it's hard to believe it's only that. Is Excel 2007 so much slower than 2003?
The macro makes quite extensive use of the .rows(Rownr).Delete method. Is the fact that 2007 has 1 million rows against the 65536 of 2003 the culprit? It has to shift much more data up when deleting a row, no?
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
I have an excel sheet that I am importing to my network software. One of the columns lists telephone numbers, but they are as follows: aaa-bbbb
I need a way to enter an area code (the same area code) for all of the numbers in the column without manually entering them (the program I am importing this list to will not work without 9-digit telephone numbers).
I am writing a code that has a sheet change event linked to call on a number of macros. The vba works fine until I change another cell and it activates the same macro.
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range
Select Case Range(BI47) Case "1"
I have tried if statements and everything else I can find, most will not work, others report errors. The code and macros will be the same on many sheets but work independently and BI27 is the source of the change