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
ADVERTISEMENT
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?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Apr 4, 2014
I have a question, I have a pivottable with data (only numbers as values). In this pivot there is a report filter which filters current month and last month. Is it possible for the pivot to show the data as a difference between current month and this month and if so where to start with the vba code to let it substract from each other?
View 6 Replies
View Related
Nov 21, 2013
I have a column with 3 different types of data in 30000 cells in Column A (Example model attached). This contains data types (i) Alpha Numeric Data (ii) Numbers only (iii) email ids
Unique identifiers for the above 3 are:
(i) Alpha numberic data : ABC
(ii) Numbers only : 1375
(iii) email ids : @
seggregate these data into 3 different columns.
View 1 Replies
View Related
Jan 12, 2009
I currently have almost 20,000 rows of data and I am filting down to a particular value in column D. However, as the filter runs you can see that after it finds the fields it keeps running like it should to the end to make sure there are no additional values. Is there some faster way to search this many lines? Would it be faster to sort the worksheet first and then do something with a sorted worksheet or what are my other options?
View 9 Replies
View Related
Jun 1, 2007
I m importing data and I have not seen anything remotely similar in any previous posts. I need to import data using an array for the different filter parameters. The size of the array will vary. The underlying problem is that VBE appears to limit the number of characters that are allowed across one row. If the array size is only one the code works fine - any larger and it crashes.
Sub ImportDataWeek()
Dim state As String
Dim Test As String
Dim NewStatement As String
Dim restate As String
Dim i As Integer
state = ""
For i = 1 To (UBound(TestType) - 1)
Test = "(qryDataRawValues.Test= '" & TestType(i) & "') AND (qryDataRawValues.Prod_ID= " & ProdID & ") OR "
state = state + Test
Next i
NewStatement = Mid(state, 1, Len(state) - 3)
restate = "WHERE " & NewStatement
Sheets("RawData").Select.....................
View 3 Replies
View Related
Jun 23, 2014
I am working on a complicated macro. The macro is suppose to move certain data set to a specific worksheet if an if statement is "True."
As of now the macro, inserts the row into a new sheet but it is inserted into Row 1. My data set is in Sheet A is up to Column U and I want it to be inserted in Column B on Sheet B. The code below inserts the entire row into Sheet B starting with column a.
[Code] ........
This variation of the code moves the entire data set from Sheet A to Sheet B and inserts it in Column B as wanted, but it does NOT follow the Criteria of "true", it brings the entire data set even those with "False".
[Code] ..........
View 1 Replies
View Related
Oct 20, 2012
PIVOT TABLES -- Old data remains in pivot table filters
One more Pivot Table Question:
I have workbooks i have built and re-use with fresh data after verifying that the previous data has been cleared. Unfortunately there are times in the drop down filter, old data will remain, but is not at all in the data set. How to clear this out? I have ensured that before pasting in new data the old data has been cleared below the headers. I keep my headers so I don't need to re-do the Pivot Tables or adjust the Name / SUM capabilities in the VALUE FIELD SETTINGS.
View 2 Replies
View Related
Oct 25, 2013
I have a pivot table whose 'output' (databodyrange?) is a nice, rectangular, 2D array. The table has many filters, each with many possible selections.
I'd like to write a function, whose parameters are the filter selections and the pivot table/data, that outputs the corresponding 2D array.
e.g. I'd call it with something like myFunction(args that determine the filter selections, myPivotTable)
Is this possible without calls to a macro? If so, how? If not, where should i look?
View 9 Replies
View Related
Jan 30, 2009
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec
.AutoFilterMode = False
.Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd
With .AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12)
On Error GoTo 0
End With
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy shtLehman.Range("A6")
End If
.AutoFilterMode = False
End With
View 9 Replies
View Related
Jul 1, 2014
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies
View Related
Jan 3, 2014
I have an Excel file with a table of data on Sheet1. The table of data has 3 fields which are Code (a Text field) , Date (a Date field) and Amt (a Number field). I'm using vba with sql code to query that table and return the results of the query to a different sheet in the same file. So just to reiterate, Excel is my data source. I've done my research and largely worked this code out.
I'm also plugging a date parameter into the WHERE CLAUSE of the vba/sql code and this date is drawn from cell D4. This way, the user can change the date in cell D4 and this will change the criteria in the WHERE clause of the sql query to give them the results they want.
However I am getting an error that reads 'Data type mismatch in criteria expression' when I run the below code, so there must be something wrong with the specific syntax I'm using to plug in the date from cell D4.
Note that I can get the code and parameter to work if I make the CODE field the parameter with the query definition line of :
query = "SELECT [Code], sum([Amt]) as Ttl FROM [Main$] WHERE [Code] = '" & Range("D4").Value & "' group by [Code]"
Sub Pull_Data_from_Excel_with_ADODB()
Dim cnStr As String
Dim rs As ADODB.Recordset
[Code]....
View 9 Replies
View Related
Feb 20, 2007
I am having a problem with a macro which has only started occuring on Excel 2000. It worked fine on Excel 1997. Basically, the Macro converts a text file (with comma delimiter) to an excel format. The problem I am having is with some of the date fields in the text file.
All dates in the Text file are in the format dd/mm/yyyy (i.e. 01/02/1990)
If I convert the Text file to Excel format manually through the wizard it correctly converts the date in Excel to dd/mm/yyyy (01/02/1990). However, If I run the Macro I have created, it converts the date to mm/dd/yyyy (02/01/1990).
This only seems to happen if the dd field is less than 12.
I attach a sample of the text file and Excel file (with incorrect dates) and also the Macro.
Basically I need the Macro to convert the dates in dd/mm/yyyy always....
View 9 Replies
View Related
Oct 30, 2008
I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:
1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results
After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following
Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................
View 9 Replies
View Related
Aug 3, 2008
I am trying to do a running total in one column with data added from the columnn before it. But I dont want the last amount in the running total to continue down the page I would like it to be either blank or zero amounts. Also if there is a zero amount in the first column the running total should show the amount previous.
View 3 Replies
View Related
Apr 4, 2007
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
View 9 Replies
View Related
Jul 3, 2007
I need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this
'Find row number for total row (2 rows down from last row of data)
endrow = Range("a35").End(xlDown).Row
sumrow = endrow + 2
'Set sum formula for the "e" column
Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")"
'copy total formulas to columns f thru r...............................
View 2 Replies
View Related
Aug 8, 2012
I inherited a spreadsheet to manage that is linked to a SharePoint table.
It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).
It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.
How can I have this run on all of the existing lines and anything added or changed in the future from the list?
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then
Application.EnableEvents = False
[Code] ....
View 9 Replies
View Related
