Unable To Delete 1 Million+ Rows
Apr 30, 2009
I've accidentally created over 1 million rows across several sheets which has sent my file size over 35mb.
I desperately need to delete these and cannot find any way of doing so.
They were created through macros copying columns and pasting them elsewhere, although I'm not sure how they decided that 4-800 rows of data was worth a million rows.
Can anyone offer a solution that doesn't entail making a new sheet and rewriting all the forumula's in it?
View 9 Replies
ADVERTISEMENT
May 30, 2014
I'm on mac Excel 2010 and i'm trying to delete rows..but for some reason they don't go! My workbook is a basic one, just filled with a bunch of formulas, i can't understand why it wont let me delete it.
When i do try to delete it, nothing comes happens and when i try to delete a large amount it comes up with 'not enough memory, continue without undo deleting rows' but my laptop has 8GB and my other laptop has 16GB so i can't see why a 16GB laptop wouldn't be able to delete it.
View 1 Replies
View Related
May 20, 2009
I have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.
View 2 Replies
View Related
Apr 10, 2013
I have a comma delimited CSV file that holds about 2 millions rows of data (a lot I know but it's the only format I can work with unfortunately). One of the rows has some numbers in it that have lots of decimal places (like 3.908651901). I need to round that entire column - all 2 million rows - to whole integers.
The problem is that when I open it in excel it says something like "only 1 million rows shown". After more investigation I have found out that excel cannot handle more than 1 millions rows at a time.
My question is this: If I select the whole column and round the numbers to integers will that apply to the entire 2 millions rows? Or only the 1 million showing in excel?
Are there any other programs available that can handle 2-2.5 million rows and change the column to whole integers?
View 2 Replies
View Related
Jan 30, 2013
code to import a tab delimited text file with about 3 million rows so that it creates a new tab every time it hits the 1 million row limit?
View 3 Replies
View Related
Feb 14, 2014
I have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.
View 4 Replies
View Related
Feb 9, 2009
I have a comment which I wish to edit, but I am unable to either edit or delete the comment. I have checked the 'shared workbook', 'shared worksheet', and 'protected cell' options and they are all turned off (never had them on).
The cell I have the comment on has a dropdown box, but I can't see that being a problem. I am able to add a comment, which I can edit and delete, but the original comment seems to be 'stuck'.
View 3 Replies
View Related
Dec 11, 2013
I'm trying to delete images from an excel spreadsheet. She had imported several Red "X" symbols and placed them at the bottom of the spreadsheet so that she could click and drag them to various spots on the spreadsheet when needed.
Well, now wants several of the "X" symbols to be erased...the problem is that they won't simply delete and when I click to drag them they stay in the same place and give me a duplicate to drag.
EXCEL Example.xlsx
View 3 Replies
View Related
Apr 24, 2007
Using two Combo Boxes, I am trying to create an drop down outline form input sheet.
What it does is if you select the first selection in the combo box 1, then it hides certain rows, and deletes certain cells. If you select the second option in the combo box 1, it shows another combo box (2) which has another list of categories.
When I select the option 2 on the combo box 1, it unhides a bunch of rows, and activates combo box 2. However, when I try to select categories on combo box 2, each time it tries to hide/unhide rows I get this message "Unable to set the hidden property of the range class." I have no idea whats going on.
Here is my ....
View 9 Replies
View Related
Sep 27, 2011
Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
Running Office standard 2010, Windows 7 pro x64
View 8 Replies
View Related
Apr 7, 2013
I am unable to insert sheet rows or columns in a microsoft office spread sheet in a specific file. Other files are Ok.
View 14 Replies
View Related
Nov 11, 2013
It was suggested that cells formatted as lists will now allow new rows or columns. I don't think this is case but not sure how to tell? Other suggestions were merged cells or cells protected. Not sure if any of these are the case either.
View 5 Replies
View Related
Oct 27, 2008
Is it practical to attempt to iterate over all cells in Application.Cells when using Excel 2007? I am truely amazed by the number of cells available but slighty unsure of the practicality of a spreadsheet which attempts to use of the 17,179,869,184 cells in each worksheet!
View 9 Replies
View Related
Aug 11, 2013
I am unable to unhide some rows on a sheet. Row 1 though 34 the row numbers are blue. After that row 65 and on are black. I can unhide columns but not the rows. By the way row 35 through 64 are missing or hidden. How do I unhide everything?
View 2 Replies
View Related
Aug 3, 2009
See attached sheet "Selected estimate", Cell B4 and Down, This was working before I made some edits to the "checklist" sheet. I have hit C+S+E a million times and I cannot figure out how I broke this forumla. Btw, this is excel 2007
View 2 Replies
View Related
Jun 10, 2008
What I am trying to do is, (I'll generally explain it and will go in details when needed) I have four tabs, but right now I will focus on two tabs. The first tab is called DATA_INPUT, will be the raw data inputted by users, approximately 45,000 to 50,000 rows. The second tab is called DATA_SET has data that are already set from previous reconciliation, this tab has approximately 10,000 rows.
my goal is to do a search from data_input starting with row 3 all the way to the last row, and search to see if any of these data are in the DATA_SET tab. Vlookup will not work if any of you are thinking about this, b/c the datas are in phrase and the data_set data are in chunks of that phrase (i'll explain in detail if needed).
So far I've created a loop code that works and gets me the result I want, but the problem with the data's being so huge, the loop will be looping apprx 500 million times, this cause the program to run very very low. For example to run 1000 data from DATA_INPUT it takes apprx 30 minutes or more and sometimes b/c this is so huge it crashes. i'll already did the calculation of how many loop it has to run through to finish, it's about 500 million loops (50000*8000 = 400,000,000 plus looping through the phrase to find the word match, approx 500 million loops).
SO my question is how can I make my program run faster since it need to loop so many times, or if you have a better suggestion in solving this problem, let me know....
View 14 Replies
View Related
Oct 7, 2008
Will some one guide me, How can i covert my currency (Rupee) in to Million?
(2700000 Rs Into Million with formula/automatically)
Secondly,
Can i make in a specific cell 4 colums & 2 rows ? Is it possible. ?
View 9 Replies
View Related
Mar 5, 2008
Cell I5 has the sum of cells A5:H5. Every time the value of cell I5 reaches the next million, I'd like for the date that it reaches the next million to be displayed in J5.
View 9 Replies
View Related
Jul 15, 2009
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
View 3 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
May 22, 2008
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
May 10, 2012
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
View 1 Replies
View Related
Sep 30, 2008
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
View 9 Replies
View Related
Aug 28, 2007
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
View 2 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
Jun 21, 2008
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies
View Related
Nov 5, 2006
Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
View 2 Replies
View Related
