Deleting large numbers of rows

[dchun]

Greetings

I have several spreadsheets with more than 600K rows and one column. In each spreadsheet, 100,000+ rows contain only '| | |' and I need to delete these rows. I have used a simple formula to put the word 'TRUE' in column A of each row that I want to delete, making just two columns.

Not sure what I may be doing wrong. I can filter and display the rows I want to delete, but if I try to select them, Excel quits responding, same thing with the "Find" function, I can find all the rows to be deleted, but if I try to actually select them, Excel quits responding.

Searching these forums, I have found several Macros that seem like they should work, but none of them will. Some will not run, others start running but Excel stops responding pretty quickly. I think it may be the volume of data that I am dealing with that is causing my problems.

Does anybody have any suggestions?

Thanks!
Doug

[Jakobshavn]

What formula did you put in column A?

[AlphaFrog]

I think there is a limit to non-contiguous areas Excel can select. If I remember correctly its something like 8000 areas but I'm not sure. So if you have alternating TRUE-FALSE rows where you would have more than 8K TRUE areas, then Excel may not be able to select all those areas.

One solution is to sort column A so all the TRUE rows are contiguous.

This sorts the TRUE rows first, then deletes the TRUE rows.

Please Login or Register  to view this content.

[dchun]

=IF(A2="| | |",TRUE,FALSE)

I may have the order reversed, trying to restart Excel....AGAIN!

[Jakobshavn]

Rather than use a filter, start this small macro:

Please Login or Register  to view this content.

Go get a cup of coffee or tea and return when the macro is finished.

[dchun]

Oh, but before I tried to run any macro I copy that column and past values so the cell contains "TRUE" and not the formula.

[dchun]

Macro is running. How long should something like this take to run - appx. 110,000 rows to be deleted. I need to know so I will have an idea when "Not Responding" really means "Working"

[AlphaFrog]

Macro is running. How long should something like this take to run - appx. 110,000 rows to be deleted. I need to know so I will have an idea when "Not Responding" really means "Working"

Did you try the sort-filter-delete macro in post #3? It should be fast. I have Excel 2003 on a turtle of a computer and it deleted 30K rows in about a second.

[dchun]

Great, thanks. That only took about 15 Minutes. I am going to try something else now. I have been filtering and deleting out blank lines, lines with '|', and lines with '| |' manually before running any macro (takes about an hour.) I can use the formula =OR(B1="",B1="|",B1="| |", B1="| | |") to get 'TRUE' in A for all those conditions then run this macro. I can't sort because when this macro finishes, I need to run another one that will move data up to the end of the previous row - its a long story. But if I can cut massage time significantly that would be a huge help. I will post the results.