Delete row if blank cell on very large data sets (Macro just deletes all data) code attached

[Slomaro2000]

Hello all,
I have very large datasets 100k or more. I need the row deleted if the cell a cell in say column A is blank.

These codes work on data sets that are not huge.

Sub Delete1()
Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Same happens when I just filter by blank and try to delete the rows. Crashes and deletes everything.



Could run this but this takes around 10min for 100k rows. Anything faster? I have looked and looked with no luck.

Sub one()
    Dim j As Long
    For j = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    If Cells(j, "A") = "" Then Cells(j, "A").EntireRow.Delete xlUp
    Next j
End Sub

 

[ChrisBM]

Let's say your data set has a headrow in row 2

Rows("2:2").AutoFilter  Field:=1, Criteria1:="="

Then just select and delete rows...

-------------------------------------

Alternately, turn off Screen Updating for a faster run time (Application.ScreenUpdating = False)

 

[Andrew Poulsom]

The problem you are seeing is described here:

SpecialCells limit problem

 

[Slomaro2000]

Thank you Andrew.