I am trying to delete all rows that have blank cells in column A in a long dataset (over 60 000 rows in excel)
I have a VBA code that works great when I have less then aprox 32 000 cells:
Sub DelBlankRows()
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub
Does anybody know a way so that it works on a large number of rows?
Thank you
Using a Macro to Delete Rows Based on Cell Values The overall process is two simple steps: The first step is to filter the rows based on filter criteria for the values to be deleted. Then the macro deletes the visible cells in the range.
In the "Go to special" dialog, choose "Blanks" radio button and click OK. Right-click on any selected cell and select "Delete…". In the "Delete" dialog box, choose "Entire row" and click Entire row.
Go ahead to right click selected cells and select the Delete from the right-clicking menu. And then check the Entire row option in the popping up Delete dialog box, and click the OK button. Now you will see all the cells containing the certain value are removed.
You could try:
Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
Application.ScreenUpdating
toggles whether updates made in code are visible to the user, and trying Columns("A:A").SpecialCells(...
might save time because it doesn't actually have to select the cells - untested.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With