Vba Running Slow

Aug 27, 2007

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?

ADVERTISEMENT

Slow Running Macro

Oct 3, 2007

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.

View 9 Replies View Related

Slow Running Code

Nov 6, 2008

My code runs so slow! The sheet only has 233 rows and 6 of them are not in the loop.

View 6 Replies View Related

Macro Running Very Slow

Jun 11, 2009

I 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 Related

Slow When Running Macros

Mar 21, 2008

I 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% ...

View 9 Replies View Related

Slow Running Spreadsheet

Jul 31, 2006

I have quite a large workbook, which runs very, very slowly when I'm on a certain sheet.

Scrolling seems to lag, and there is a long delay between me clicking a cell and it becoming active.

The sheet contains a number of web queries, and a number of cells which refernce other cells, both in the same worksheet, and other worksheets.

View 3 Replies View Related

Macro Running Slow

Jan 30, 2007

I have a macro that isn't very complex but it is running very slow, does anyone know any tricks on how to speed it up?

View 9 Replies View Related

Data Filters Running Very Slow

Oct 5, 2009

I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.

I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.

There was no problem until I made a couple of changes last week:

1. Adding a simple pivot table

2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.

I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.

View 12 Replies View Related

Slow Vba Running With Xlsm Files?

Aug 29, 2013

i have a simple question regarding vba code involving opening/eleboration/closing of different excel workbooks' format. I saw that with xls/xlsx the macro works with relatively high speed, but it is very slow when it comes to xlsm file. Is there an explanation? Does a command which enhance speed for xlsm file exist?

View 8 Replies View Related

Users In Excel :: 50+ Running Slow

Dec 2, 2009

I have built an Application in Excel that is used by a userbase of 50+ people. Now, as standard, they are only able to access the document read only. The application writes data to an ".mdb" but the user cannot change the application directly istself.

Now, we've had the problem over the last few days that the program is running slowly when it is trying to write data back to the ".mdb" file.

I believe that it could be because connection to the sever that our application is running slowly (or the server is too highly trafficed) but our server team are telling me there is nothing wrong with the server and it's running slowly because there are too many people in one Excel application/

View 9 Replies View Related

Copy/Paste Code Running Very Slow

Apr 4, 2009

The code opens a varying workbook with the same format as the source workbook and copies specific columns into the source workbook.

It is currently taking about 20seconds to run.

The worksheets are protected and have merged cells hence the call TestMe line.

View 13 Replies View Related

Macro Running Very Slow - Cell (press Esc To Cancel)

Dec 10, 2009

I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:

Cell (press esc to cancel)

Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?

View 3 Replies View Related

Excel 2010 :: Legacy Macros Running Slow?

May 26, 2011

I have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.

However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:

Code:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

ActiveWorkBook.PrecisionAsDisplayed = False
It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!

Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't work and anyway I loose things, like data validation, when I save it in Excel 2010.

View 4 Replies View Related

Workbook Slow Down

Mar 30, 2009

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.

View 9 Replies View Related

Slow Calculation

Dec 5, 2009

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

View 14 Replies View Related

Slow Spreadsheet

Nov 14, 2006

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

View 9 Replies View Related

Slow Saving - VBA

Mar 14, 2007

I'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

View 9 Replies View Related

Slow Calculations

Mar 19, 2007

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?

View 9 Replies View Related

Slow VBA Module

Sep 24, 2009

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?

View 9 Replies View Related

VLOOKUP In UDF Slow

Nov 10, 2009

I 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?

View 9 Replies View Related

Workbook Slow To Open

Jul 8, 2009

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.

View 14 Replies View Related

Slow Reaction On Cell Changes

Aug 24, 2009

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.

View 14 Replies View Related

Very Slow To Load Files

Dec 17, 2009

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.

View 12 Replies View Related

Slow Update Workbook

Feb 5, 2010

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?

View 9 Replies View Related

FileCopy Extremely Slow

Nov 6, 2008

I 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.

View 2 Replies View Related

Spreadsheet Runs Slow..?

Dec 12, 2008

I 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 Related

Slow Delete Row Macro

Mar 9, 2009

I 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 Related

Very Slow Small Vba Code

Mar 27, 2009

Very slow small vba code. I have the following

View 2 Replies View Related

Speed Up Slow Macros?

Apr 4, 2007

I use the Application.ScreenUpdating = False all the time. Any there any other things like this which speed up macros?

View 9 Replies View Related

Slow Code Execution

May 9, 2007

I 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.

View 9 Replies View Related

Slow Writing To Cells

Jul 6, 2007

A 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)

View 9 Replies View Related

Slow Inefficient Code

Aug 4, 2008

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

View 9 Replies View Related

Large, Slow Workbook

Aug 31, 2008

I 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?

View 9 Replies View Related

Macro Runs Slow

Nov 13, 2008

The 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...................

View 9 Replies View Related

Trim Function Is Slow

Feb 5, 2009

Due 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?

View 9 Replies View Related

Slow For Loop With IF Statment

Nov 23, 2009

why 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

View 9 Replies View Related

Spreadsheet Very Slow To Calculate

Jan 3, 2010

I have a blank workbook that I am just about ready to deploy, but it is very slow to calculate. I know I can manually calculate, but I would rather the workbook just be fast and calculate on its own. The workbook is blank now, but will be used all of 2010 and will ultimately end up being around 20,000 lines. So, I have built in the formulas and conditional formatting into the 20,000 lines now so I don’t have to continually add lines.

So, the questions are…

What part of the workbook is slowing it down?

Here is how the tabs breakdown:

NOTESJust text no formulas

DATA20,000 Rows
1 formula of conditional formatting (Covers entire 20,000 rows and all columns except A)
4 columns with formulas (2 using VLOOKUP, 1 using SUMIF, 1 simple addition)

REPORT2 pivot tables linking to DATA

View 9 Replies View Related

Do LoopUntil Slow Speed

Mar 6, 2010

I have written a macro which is sucessful in the sense that it does what I need. However, I ran it against my data for the first time today and it took forever. To give you an idea of the data size... my code had to loop through about 10,000 rows and move the ones that met the IF criteria to another sheet.

Can anyone provide any suggestions as to what might make my code faster? Should I take a different approach? I am definitly still wet behind the ears.

My code is below...

Dim count As Integer
count = 3
PeCount = 3

ActiveWorkbook.Worksheets("MyWorksheet").Activate

Do

If Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "1111111" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "2222222" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "3333333" Then

Sheets("MyWorksheet").Rows(count).Select
Selection.Cut
Sheets("YourWorksheet").Activate
Sheets("YourWorksheet").Rows(PeCount).Select
Selection.Insert Shift:=xlDown
Sheets("MyWorksheet").Activate
Sheets("MyWorksheet").Rows(count).Select
Selection.Delete Shift:=xlUp
PeCount = PeCount + 1
Else
count = count + 1
End If

Loop Until IsEmpty(Sheets("MyWorksheet").Cells(count, 2).Value)
End Sub

View 9 Replies View Related

Slow Execution Before Interruption

Jun 21, 2006

I have a macro that opens various files (via a loop) and then goes to each worksheet (via another loop) and looks through each row of data (a third loop).

Each worksheet can have over a thousand rows of data. Once I hit the button for the macro to run, it runs without any error messages, but it seems to take forever.

When I hit escape twice to interrupt the macro and then press the 'play' button, it zips through and finished running the macro really quickly. The interruption point is generally somewhere within the third loop.

It's almost as though it falls asleep and I have to wake it up.

View 9 Replies View Related

SUMPRODUCT Causes Slow Calculations

Jan 5, 2007

I made one excel file with 9 sheet. there are used sumproduct formula in 200 rows.

i am not used any vba macro for this file.

i am used sumproduct formula link to other files and other sheets.

my file size is 1.80 mb but when i am enter new data in file then file going in process of calculation and calculate all forumula it take some time. my file options calculation is automatic but for calculation it take time and i have to wait for that for Process.

There are any macro for calculation so my file work fast and don't want to wait for calulation process .

This file is not open like other files. it is take a time in update the link or calculation.

View 9 Replies View Related

Slow Macro Loop

Jan 10, 2007

Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.

Dim cell As Range
For Each cell In Range("TPD")
If IsNumeric(cell) And Not IsEmpty(cell) Then
If cell.Value > 0 Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
End If
On Error Goto 0
Next
End Sub

View 2 Replies View Related

ADVERTISEMENT