VLOOKUP In UDF Slow
Nov 10, 2009I have a function grabbing latitudes and longitudes from a zip code look up. The table array used is approximately 48k rows.
Is there a way to speed up the lookup?
ADVERTISEMENT
Vlookup Array Formula Too Slow - Don't Want VBA
Apr 28, 2007I have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER.
What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.
Vlookup To .xlsm Macro Is Incredibly Slow
Jul 23, 2009This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.
This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?
Workbook Slow Down
Mar 30, 2009I 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.
Slow Calculation
Dec 5, 2009I 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
Slow Spreadsheet
Nov 14, 2006My 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
Slow Saving - VBA
Mar 14, 2007I'm rather new at VBA
I have a small workbook with a userform and some buttons. Each time I press a button a counter increases and the workbook is saved.
This saving seems much too long for such a small file.
I use
Activeworkbook. save
Slow Calculations
Mar 19, 2007I'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?
View 9 Replies View RelatedSlow VBA Module
Sep 24, 2009This 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?
Vba Running Slow
Aug 27, 2007I 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?
View 2 Replies View RelatedWorkbook Slow To Open
Jul 8, 2009I 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.
View 14 Replies View RelatedSlow Reaction On Cell Changes
Aug 24, 2009I 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.
Very Slow To Load Files
Dec 17, 2009i 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.
Slow Update Workbook
Feb 5, 2010I 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?
View 9 Replies View RelatedSlow Running Macro
Oct 3, 2007Below 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.
Slow Running Code
Nov 6, 2008My code runs so slow! The sheet only has 233 rows and 6 of them are not in the loop.
View 6 Replies View RelatedFileCopy Extremely Slow
Nov 6, 2008I am trying to make a macro that copies a file per row to a folder specified by a cell in the row (and then calls a command line tool on it). I am using FileCopy to copy the file, but it is extremely slow even for only 6 rows (I need it to work with hundreds).
I think the problem is that the folders are network folders. Is there maybe another copy file function in VBA that does not block while the operation completes? I could go through all my rows and just copy the files, then go through again and run the command line tool. By the time it reaches the end of the sheet, the first file would be done copying.
Spreadsheet Runs Slow..?
Dec 12, 2008I created this spreadsheet to assist with pricing for my towing company. I struggled with getting the dropdown lists and combo boxes to work, and I created and deleted many of them before I got it to work like I wanted, but I think I left a mess. It runs slow, showing a delay when I click on different radio buttons, and there is an exclamation point in the Excel icon in the file directory. Could someone take a look in the code structure and tell me if there are any opportunities to clean it up. RateCalcApp.xlsm
View 2 Replies View RelatedSlow Delete Row Macro
Mar 9, 2009I have got the macro below, my problem is that when you run it, it takes 20 seconds or so. In my other larger spreadsheet it was almost instant.
View 4 Replies View RelatedMacro Running Very Slow
Jun 11, 2009I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
View 7 Replies View RelatedVery Slow Small Vba Code
Mar 27, 2009Very slow small vba code. I have the following
View 2 Replies View RelatedSpeed Up Slow Macros?
Apr 4, 2007I use the Application.ScreenUpdating = False all the time. Any there any other things like this which speed up macros?
Slow Code Execution
May 9, 2007I have the following piece of
Sub Summarise_Variations()
Dim myRange As Range
Dim Write_Cell As Long
With Application
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
End With
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.
Slow Writing To Cells
Jul 6, 2007A macro in workbook 1 creates a report by reading & manipulating data from two workbooks(WB 1 and WB 2).
A new blank report sheet is copied from WB1 to WB2. The data (some text, some numeric) is collected in a 20 element, single dimension array & written to the new sheet in 20 columns using a for/next loop. This is repeated for many rows(can be more than 1000).
The array is "erased" before each row. Screenupdating is turned off, calculation is turned off. The report takes longer & longer each time it is run - Why? With the one-and-only "Write" line "remmed out" the report takes just 2 secs however many times it is re-run - including the copying of the blank master report page. If the first report takes, say, 1 min the second time takes 2 mins & the 3rd - 3 mins etc. Reset is only achieved by exiting excel. The code in question is:- For col = 1 to 20 : cells(ro,col) = d(col) : Next col. (ro = the current row number which is incremented for each pass, d() is the data array)
Slow When Running Macros
Mar 21, 2008I have a simple Macro. I assigned to a Form button.
when i click that button systems takes lots of time to complete the macro.
left down task bar it say calcuating cells and load from 1% to 100% ...
Slow Inefficient Code
Aug 4, 2008way 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
Large, Slow Workbook
Aug 31, 2008I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.
Is there anyway of compressing it or making it run faster and not slow down the computer?
Macro Runs Slow
Nov 13, 2008The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
Trim Function Is Slow
Feb 5, 2009Due to the fact that some of the raw data I get dumped out of my database has leading and trailing spaces it is necessary to trim them. The one part of my code which seems to take forever to run is the section which performs this function on around 22,000 cells in one column. Here is the code I'm using. Is there a better way to do this?
'Set ServiceType = wsTmp.Range("C2:C" & tmpRows)
'
'For Each cell In ServiceType
'cell.Value = Trim(cell.Value)
'Next cell
The thing, with much code, seems to be that the slowness of my computer in executing code is directly proportional to the amount of uptime on the machine. Often a cold start will remedy the issue. Rather than having to do this daily to remedy the situation is there some other remedy to the problem?
Slow For Loop With IF Statment
Nov 23, 2009why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds