Excel 2007 : Blank Rows At Bottom of Sheet Will Not Delete

Hello Helpful Excel Experts,

My macro starts out with 140,000 rows of data on Sheets(1). A formula is pasted into the entire 140,000 cells in Column E, and results in values. At a later point, a different formula deletes a bunch of duplicates, and I am left with 100,000 rows of data, and 40,000 blank rows at the bottom of the sheet. Well, kind of blank. Either because they had values or formulas or both, the rows are in this hellish limbo where they're blank but they're not. To get rid of these rows, I have unsuccessfully tried the following:

The standard formula (it ran, but nothing happened):

Please Login or Register  to view this content.

I have also tried to filter various columns to click on the "blank" option, and then select and delete all of the filtered results. But the "blank" option does not show up.

I Hit F5, selected Special Cells, and then blanks. All of the blank cells at the bottom of the sheet highlighted. (Yay!) I clicked "Delete Entire Row". The 40,000 blank rows are still at the bottom. The scroll bar at the right indicates this, as well. I'm not just scrolling past the bottom of the page.

There are no formulas visible in the 40,000 blank cells. Nevertheless, I again went with that F5 Special Cells/Blank Cells option, selected all blank cells after my data, hit copy and hit paste values, just to clear all formulas. Then I deleted entire row. The 40,000 rows are still there.

I manually scrolled to the bottom of my data, selected the 40,000 blank rows, and deleted. The 40,000 rows are still there.

Finally, I thought to myself "This is ridiculous. I can't believe I didn't just copy and paste all of the usable data onto a new tab." So I added Sheets(2) and did this:

Please Login or Register  to view this content.

So satisfied with myself. Until I went to Sheets(2), and found....40,000 blank rows at the bottom of the sheet.

Okay. Since the 40,000 blanks are included in the SpecialCells(xlCellTypeLastCell), I deleted my new Sheets(2) and set the macro to loop through Column A, deleting Entire Row for all rows where the value of the corresponding Column A cell was less than 1. I didn't want to loop, because I had so much data that it would take forever to finish. But I did it. And the loop ran, continuously, but no blank rows are deleted. I'm not sure if I did the formula, correctly:

Please Login or Register  to view this content.

Can anyone crack this? This is one of many daily reports. I cannot manually copy 100,000 variable rows of data and paste them onto a new sheet, every day.

Can you post the formula you use to get rid of duplicates?

Have you tried using Text To Column and then trying to delete?

It may also have to do with the formula that inserts stuff into column E

Here is the formula I use to activate the built in function to delete duplicates:

Please Login or Register  to view this content.

Text to columns and then delete? How would I achieve this? I've only ever used this to transition data filling a cell into data filling a row.

Also, in case it matters, here is what I used for the original formula in Column E, before deleting the duplicates:

Please Login or Register  to view this content.