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):

'Delete Row if Column A Cell is blank 
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
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:

TheEnd = Range("A1").SpecialCells(xlCellTypeLastCell).Row
With Sheets(1).Range("A1:I" & TheEnd).Copy
With Sheets(2).Range("A1").PasteSpecial
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:

'Delete Blanks
TheEnd = Range("A1").SpecialCells(xlCellTypeLastCell).Row
With Sheets(1)
For Each r In Range("A1:A" & TheEnd)
If r.Value < "1" Then r.EntireRow.Delete
Next
End With
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.