Excel VBA: Delete entire row if cell in column A is blank (Long Dataset)

excel delete-row vba
user1783504 picture user1783504 · Oct 17, 2013 · Viewed 82.6k times · Source

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

Answer

ForkandBeard picture ForkandBeard · Oct 17, 2013

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.

Related questions

How to resolve error 400 in excel-vba

I am writing an excel-vba to find and delete entire row if having blanks cell in a particular column. My macro is working fine if there is at least one blank cell but showing error 400 if there is no blank …

Read More
VBA: Deleting Rows with a Specific Value

I'm trying to write a macro to delete all rows that have "True" in column A. Here is what I have so far: Sub deleteBlankRows3() Dim lastrow as Long Dim x as Long lastrow = 4650 For x=8 to x=lastrow If (…

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More