How Many Records To Sort Without Crashes
Oct 3, 2009
I have a column of data that is an 850,000+ list of phone numbers. I need to peridoically insert another 10,000+ at a time and see if there are duplicates. Using conditional formatting to highlight the duplicates with a colored cell, and then sorting those records to the top is what I want to do, but it's too much for Excel to handle, and it hangs. It looks like I need to break up the 850k records into smaller chunks. Anyone know what an average pc with Excel 07 can handle? It's a 2.8ghz w/2gb ram.
View 6 Replies
ADVERTISEMENT
Jun 3, 2009
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:
Run-time error ‘1004’:
Sort method of Range class failed
When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
View 7 Replies
View Related
May 10, 2007
I am using vb6 and I need to Sort records in spreadsheet based on items in column 7. Actually, I want to sort the spreadsheet before executing the count process below. Note: the code below works perfectly, I just need to sort the spreadsheet before counting.
Selection.Subtotal groupBy:=7, Function:=xlCount, TotalList:=Array(7), _
PageBreak:=False, SummaryBelowData:=True
In otherwords, I want to sort the records based on the 7 column before executing the above routine.
View 2 Replies
View Related
Mar 1, 2008
I have data records that are 3 rows long by 18 columns (A thru R) wide. People's 1st names only exists in the 1st row, 2nd column (B) of each record. The 1st record starts on row 5.
I need a VBA procedure that when run, it will sort the data alphabetically by the 1st row, 2nd column (B) of each data record. The 3 line data records looks like this starting on row 5:
________A__|_____B___|____C____|___D___|___E___|___F___|___G___|___H___etc
1st row _18_|_John____|_730-6853_, etc
2nd row ____|_Doe____|_238-4835_, etc
3rd row_____|_Sales___|_328-4893_, etc
1st row _32_|_Jack____|_684-3812_, etc
2nd row ____|_Jones___|_232-4954_, etc
3rd row_____|_MFG____|_238-5355_, etc
1st row _18_|_Jimmy___|_485-2294_, etc
2nd row ____|_Smith___|_223-9110_, etc
3rd row_____|_Maint___|_368-6381_, etc
If only above 3 records exists, when sorted Jack's record starts on row 3, Jimmy's record starts on row 6, John's record starts on row 9, etc
We can have as many as 15 records (3 lines long) that goes thru row 49.
View 9 Replies
View Related
Apr 19, 2008
I have around 50,000 records in a data file within which groups of records are contained in blocks delineated by date/time. e.g.;
A B C D E
10/03/2008 13:15 a 100.0
10/03/2008 13:15 b 2.8
10/03/2008 13:15 c 50.5
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:25 75.2 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 3.5
etc
As you can see, while the file is ordered by date/time in Column A, the values in column E are randomly ordered.
My objective is to maintain date time order but sort the values in Column E into an ascending order within each block. So, for example, using the details above I would want it to look like;
A B C D E
10/03/2008 13:15 b 2.8
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:15 c 50.5
10/03/2008 13:15 a 100.0
10/03/2008 13:25 3.5 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 75.2
Note that there is data in columns B to D which would need to be sorted in line with Column E. So, all records in the block, cols A to E are sorted to give the above order for each time block.
My problem is that I'm not sure whether a basic sort can do the job or whether a macro is needed. I'm using Excel 2003.
View 4 Replies
View Related
Feb 13, 2008
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
View 3 Replies
View Related
Aug 10, 2014
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
View 7 Replies
View Related
Apr 25, 2014
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
View 5 Replies
View Related
May 7, 2006
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
View 7 Replies
View Related
Apr 14, 2009
I have the following code which filters data in a sheet, and loads the numbers into textboxes on a userform. There are two multipage controls, each with several textboxes on them. I have commented out several lines because if I leave any of them in, it crashes excel.
It will work once. But if I close the workbook, re-open it, and run the code again, it crashes.
The thing I find really interesting is that all the 'problem lines' were added in as part of a retro-fit. The person I created this workbook for needed one more column of data. I followed the same programming pattern as what I used on the other, but only these ones seem to cause problems. Any thoughts?
NOTE: The message boxes were just to help me figure out which lines of code were causing the crash. ....
View 9 Replies
View Related
Mar 5, 2007
My main workbook basically starts with, Sub macro1 (), Do Until, Web Query, many many many calculations in between and finally ActiveWorbook.Save.
After ActiveWorbook.Save it will start the same process with a new web query and works all perfectly and perpetually just as I would like it to....
View 9 Replies
View Related
May 14, 2007
I'm trying to use a listbox and wanted to have the data sit in a different sheet. When I do this, I get a message about low memory and then excel says it has an error and shuts down. When I move the data to the same sheet as the listbox, there is no problem.
I'm using excel 2000 and that's all I can get (at work). Any thoughts? Should I just put the data on the same sheet and hide it? Is there a way around this?
View 9 Replies
View Related
Oct 19, 2007
Just recently Excel crashes about 4 out of 5 times. Any advice to where I should start looking and how to handle this please?
View 7 Replies
View Related
Jan 4, 2008
It is a simple form which has a Module/Macro to load the form on clicking a shape.
Although the form works fine for me it occasionally crashes for other user.
The Debug says that there is an error in the Module with the line "frmCallData.Show".
Below is the Code for the form and also the Module: ....
View 6 Replies
View Related
Jun 6, 2008
On one laptop Excel crashes, without warning. It happens during copy/paste job (from one workbook to another). But not every time she copies something.
It is completly the same laptop like others in my company. My user has deadlines, and lot of copy/paste job.
View 8 Replies
View Related
Jan 25, 2009
Whenver I open the Excel App it crashes down ? Why ?
I Deactivated all the add ins, but the problem still exists.
Deleted the registery of Excel and un- install the Excel App then Re-install it again, but the problem still exists......
View 8 Replies
View Related
Feb 12, 2009
I wrote some VBA code detailed here. Is there any Excel Crash log I can look at? I have tested it on three computers. It worked on my computer just fine, but when testing it on the two other users of this spreadsheet it crashes when they click the mouse on a list box after about 5 to 10 clicks.
View 9 Replies
View Related
Dec 3, 2009
I have the following code that (should) either save the file with a generic name if a value exists in a certain field, or else save the file as the contents of the value in the field, with a message box appearing in either case. The problem is, the message box appears twice (not once) and then Excel crashes! Can anyone tell me what's going on? The code is:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FSR As String
FSR = Range("I7").Value
If FSR = "" Then
MsgBox "Service Report Number not entered."
ActiveWorkbook.SaveAs Filename:="Service Report Navilas.xls"
End If
If FSR "" Then
MsgBox "This action will save the file as the Service Report Number."
ActiveWorkbook.SaveAs Filename:=FSR & ".xls"
End If
End Sub
View 6 Replies
View Related
Jun 8, 2009
As I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!!
So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use.
Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc?
Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc.
I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
View 2 Replies
View Related
Jan 25, 2010
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes.
I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
View 10 Replies
View Related
Apr 2, 2007
I'm trying to close out excel through a user form (excel is hidden) button but excel crashes when it reaches the application.quit line of code.
I searched google and found one message that said that if you have add-ins that they can cause excel to crash when closing but there was no explanation or work around.
Is there a way around this problem?
View 7 Replies
View Related
Dec 15, 2007
I have a very strange error at present. I am saving a sheet as a file using VBA from within Excel and when I run the program from the environment of my flash drive it works without a problem but when I run the same code on my main PC it causes an error :
Run time error 1004
Method SaveAs error ... and it causes Excel to crash and then recover the file.
The code that causes this is shown below : ....
View 6 Replies
View Related
Feb 15, 2008
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
View 9 Replies
View Related
Jun 17, 2008
I open Excel. I click Insert-> Comment. Excel crashes. I try to open an excel file with comments, Excel crashes. I have already uninstalled and reinstalled, Detected & Repaired. Nothing appears in the event viewer. I am at my wits end.
Any ideas on what this could be? My only guess is a windows issue vs. an excel issue, but I thought I'd at least ask.
View 6 Replies
View Related
Feb 6, 2009
I have written a fairly large spread sheet, and I have consistently had excel crashes when the first form initialises at startup.
However if I resave the workbook over itself or sometimes as another name it will then work. Once it works - it will always work while I dont restart my computer. The next day the same workbook will crash on form startup. Once this happens t always crashes on startup. If it works OK first up it always works. However if I edit it the process may happen all over again. I am 100% their is no error in the code as in certain stages it has worked for months. It seams like some bug in excel?
Has anyone ever had this issue?
View 9 Replies
View Related
Jul 14, 2006
I run a macro which runs saves the active file to a different location to ensure that I retain the original like so -
If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then
ActiveWorkbook.SaveAs Filename:= _
"H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If .................................
View 9 Replies
View Related
Apr 3, 2007
I’m launching a new workbook, exporting some stuff, basically creating a report. But then I want to add a BeforePrint event to the new worksheet. Both of the macro below works great right up until I change “Sheet1” to “ThisWorkBook”…then Excel crashes.
Sub AddCode1()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim Copybook As Workbook
Set Copybook = Excel.Workbooks.Add
Set VBCodeMod = Copybook.VBProject.VBComponents("sheet1").CodeModule
LineNum = VBCodeMod.CountOfLines + 1
VBCodeMod.InsertLines LineNum, "Private Sub Workbook_BeforePrint(Cancel As Boolean)" & Chr(13) & "if ActiveSheet.CodeName <> ""Sheet1"" or ActiveSheet.CodeName <> ""Sheet2"" or ActiveSheet.CodeName <> ""Sheet3"" then" & Chr(13) & "MsgBox(""This Electronic Outcome Review Summary Report is NOT optimized for Printing......................................
View 5 Replies
View Related
May 4, 2007
I worked really hard on this userform for a work project - my first and likely last attempt at coding!
It used to work, and then I changed a bunch of listboxes to comboboxes, and now my file gives the 'Excel has encountered a problem and needs to close' error - but ONLY when I'm working directly with the excel file and NOT when I've got VBA open.
Compiling shows no problems - I have no idea what to do because this is a very important file for work and I am a very inexperienced coder! ...
View 7 Replies
View Related
May 18, 2007
Continuing with my goal to learn VBA and while playing with the following code, I encountered strange behavior of debug statements. In the code below I have put my comments to identify where the "funny behavior" is encountered.
Will appreciate if you can help me understand. I also have a simple request/question at the end of the code below: ....
View 7 Replies
View Related
May 22, 2007
I'm having a problem with Vlookup locking up excel everytime it tries to call a userform. If I rem either the .show lines or the entire Vlookup loop it works. I suspected it was the exit handler but that doesn't seem to be the case. If I use vlookup code and data validation instead of vb vlookup it all works except for the problem of having the code in the cells overwritten. It's probably something really simple that I overlooked. Any suggestions would be welcome. Newvendin is a multi-input textbox and frmcalendar is a calendar....
View 6 Replies
View Related
