way the code below can be made more efficient? Basically, I drop down a combobox, and based on the value i select, it populates a lot of textboxes etc based on the listindex.....
Private Sub ComboBox3_Click()
On Error Resume Next
With Me.ComboBox3
If .ListIndex = -1 Then Exit Sub
Label23.Caption = Sheets("overview").Cells(.ListIndex + 2, "c").Value
Label24.Caption = Sheets("overview").Cells(.ListIndex + 2, "b").Value
Label38.Caption = Sheets("overview").Cells(.ListIndex + 2, "d").Value
TextBox1.Text = Sheets("overview").Cells(.ListIndex + 2, "e").Value
TextBox2.Text = Sheets("overview").Cells(.ListIndex + 2, "f").Value
TextBox3.Text = Sheets("overview").Cells(.ListIndex + 2, "g").Value
It takes about 10 seconds every time i choose another project
It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.
Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.
The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
There are two columns with data. These are Assessed Object and Value on Day X.
I would like to be able to have all columns on the master sheet filled out.
This means i would need the data from sheet1,sheet2 and sheet3. First off, how would I be able to get the individual objects data to match up on the master sheet without having to look for and select that one particular object? I know there must be a more effecient way where i can pick all objects with their corresponding values and excel sorts them out appropriately. How would I do that???
Due to the fact that objects may be assessed one day and not the next there will be lots of "gaps" in the data. I would like to put a zero value in a blank cell when that happens. What I mean by this is on Day 3 some Assesed objects will not be the same as those on Day 1 (or Day 2) while some will be the same but will have a different Value on that Day.
For example, car is an object that has been assessed all three days so there are no problems there. But farm has only been assessed on Day 2so I would like an automatic zero value for the days where it has not been assessed. How do i do this???
Also, for objects that have appeared for the first time (such as bread on Day 3), I need to have the previous day values at zero. How would i go about getting excel to automatically do that???
Normally I would copy and paste the data into the master sheet but im dealing with thousands of objects so thats out of the question. Thats why I would like to learn to compile this data more efficiently.
Attached is an example excel file with the 3 spreadsheets and master sheet to illustrate what im trying to accomplish.
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
I have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25
This is the code that Recorder proffers to simply take a Range of 3 Cells and make Type Font Black/Bold and to place Thin Borders inside the range and Thick Borders aroung the range.
ActiveCell.Offset(0, -2).Range("A1:C1").Select Selection.Font.ColorIndex = 1 Selection.Font.Bold = True Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click() Dim Val Val = "6" Dim valand As Range Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0) valand = Val Dim A As Range, B, C, d, E, F, G, H, I, J Set A = ThisWorkbook.Worksheets("Stageing").Range("K2") Set B = A.Offset(1, 0) Set C = B.Offset(1, 0) Set d = C.Offset(1, 0) Set E = d.Offset(1, 0) Set F = E.Offset(1, 0) Set G = F.Offset(1, 0) Set H = G.Offset(1, 0) Set I = H.Offset(1, 0) Set J = I.Offset(1, 0) TextBox1.Value = A & B & C & d & E & F & G & H & I End Sub
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade() Dim icolor, Navn As Integer Dim TargetRow, LastRow, Previous As Long Dim Target As Range MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen" Range("A5").Activate Range("A5"). CurrentRegion.Select 'Set range ActiveCell.Offset(Selection.Rows.Count, 0).Activate LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
I am using the below to open a text file and copy the used range to my working workbook. The opening of the file makes the code slow and and also it may be bcoz i am trying to paste 11800 lines of data from one file to another.
Also i can see the flickering of the screen when the files are open. How to make the code faster and more efficient.
Code: Sub readSimFile() Application.ScreenUpdating = False '//code// Application.Calculation = xlCalculationManual
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros() Dim WorkbookRust As String WorkbookRust = ActiveWorkbook.Name ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch" Workbooks.Open Filename:= _ "C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls" Sheets("Main_Overview").Select Windows(WorkbookRust).Activate Application.run ActiveWorkbook.Name & "!UpdateEntries" Application.run ActiveWorkbook.Name & "!FilterMain" 'not ask to overwrite existing file Application.DisplayAlerts = False Workbooks("CH_Revenue_2008.xls").Save Workbooks("CH_Revenue_2008.xls").Close End Sub
I have a workbook made up of worksheets containing source data, pivot tables and charts but it is now becoming very slow since I have added a worksheet with many formulas.
If I move that worksheet containing many formulas (20 columns by 150 rows) to a different workbook will that help speed both of them up? The formulas will obviously still by referring to the original workbook.
Or is it simply by using formulas it slows things down whatever you do and I will have to think of a clever way of using pivot tables instead? Are there any other things that could be slowing it down? Other workbooks I maintain are fine.
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.
Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my
My spreadsheet is chugging through the following code. The data being filtered is rows A:AC, rows 10:193 (so not an absurdly large amount of data). It takes about 15 seconds to complete the filter. Ideas?
Private Sub EuropeButton_Click() HeaderRegion = "Europe" ActiveSheet.AutoFilterMode = False Range("A2:A8").Value = 0 Range("A2").Value = "TEG" Range("A3").Value = "TUK" Range("A4").Value = "TEF" Range("A10").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:A4"), Unique:=False If Columns("E:F").Hidden = True Then Columns("E:F").Hidden = False End If End Sub
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
This module consists of app. 25 sub routines. Every sub routine is a For-Next Loop, with 70 For-Nexts to work through. The loops check certain input cells and if these cells are empty, the row is hidden. I have the reverse also where I can unhide these rows.
When I had about 8 of these loops in this module, it would work quite fast and it took about a second to run one of these subs. Now that I have 25 if I run one of these subs it takes about 20-30 seconds.
Before I break them all down into individual modules just to find out it doesn't work, is there anything else I should be doing or could try to speed these subs up? If breaking them down into more than one module is the solution, how do I do this best?
I have a workbook with macros Ive created that have been running just fine for 2 years now. Ive upgraded to a top of the line new Dell D830 dual core laptop with 2gb ram and installed excel 2003 (same excel as always). Certain macros that used to take a second or two to run on a celeron dell now take 3min's! Ive stepped through the code in debug mode and found that its hanging up in simple areas like "Columns("A:A").Select" and "Selection.ClearContents". We have 16 older laptops running these exact codes just fine all day long and the 2 new fast computers with core 2 duo processors are hanging up constantly in the same places in code. Ive gone through and made sure all the "option" settings are identical in excel. Is there some system setting That I'm missing or possibly an excel setting That I may have overlooked?
I have searched the forums re my issue and have not found a relevant solution. I have a large workbook 19MB in Excel 2007. There are 400 sheets and each sheet contains various formulas and one macro. The only graphics on each sheet are color coding of some cells. The workbook takes 2minutes 55 secs to load. Once open there are no issues. Is there a reason why this is so slow or do I just have to accept that being a large file it will take this long. There is no issue with HD space or RAM as the laptop is only used specifically for this workbook. Running Intel Core 2 Duo, 160 HDD, 2mb ram.
I have a relatively complex application with a few thousand cell links and dozens of VBA macros (3.5 MB heavy). This application (one file for each project) is used by a few hundred users in about 200 projects. What I did recently (to allow updating macro program functionality in existing files) is to move all macros into an add-in.
What happens now is every time when I change the content of any cell anywhere in the file, it takes about 3 seconds(!) to leave the cell after changing it and to jump into the one below. Of course I'm getting complaints from users about this slow behaviour.
This also applies to cells in sheets that have no event macro program related to them. It applies as well to cells that are not referenced in any other cell. I tried changing the calculation method from automatic to manual - no change in speed. I de-activated the add-in - no change in speed. I have checked if there are any formulas that contain the today() or function or the like - there are none (actually, I had replaced them with links to a cell that contains today's date, which is automatically updated when the file is opened, but this was also this way in the previous version).
It seems that there is something going on that causes Excel to be busy with itself.
Does anyone know what might cause this strange behaviour? I tried everything I could think of without success and I'm lost now.
We are using Excel 2003 in an XP Professional SP2 environment and most of our computers have 512 MB RAM.
i had a file with 10,500 lines, 18,000 kb and it seemed slow to load (about a minute) every time i made changes and then saved it or reopened it.
so i broke the file up into 3 smaller files by cutting and pasting. i deleted all empty lines and columns beyond the file content. i defragged my compter. i cleared all excess format in job history - but -
one file now has 3,900 lines, 22,300 kb and takes 4 minutes to load.
one file now has 2,000 lines, 20,100 kb and takes 4 minutes to load.
one file now has 4,900 lines, 14,500 kb and takes 1 minute to load.
I have attached a workbook that I want to be updated by the user. It contains a number of vlookups that to refer to a data source on a server communal to all users. I wondered if there would be anything that could be done in the workbook to help speed up an update?
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
