Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 230
user1783504 Avatar asked Oct 17 '13 15:10

user1783504


People also ask

How do I delete a row based on criteria in Excel VBA?

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.

How do you delete entire row if cell is blank?

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.

How do you delete a row if a column contains a value?

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.


1 Answers

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.

like image 156
ForkandBeard Avatar answered Sep 19 '22 02:09

ForkandBeard