8

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

Community
  • 1
  • 1
user1783504
  • 331
  • 4
  • 7
  • 14
  • IT deletes the entire worksheet – user1783504 Oct 17 '13 at 15:53
  • 1
    I just did it with the full rows excel can hold with no error, I did get rid of select though, as in: Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete – Joe Laviano Oct 17 '13 at 16:15
  • so is the sub not properly working because it deletes the entire sheet or what is the actual problem with it? The speed? –  Oct 17 '13 at 17:05
  • 3
    `SpecialCells` has a significant flaw when used in VBA in Excel 2007 and earlier, it cannot handle more than 8192 areas. See [Ron De Bruin's comments here](http://www.rondebruin.nl/win/s4/win003.htm). If all your rows are being deleted then this is likely to be your problem – brettdj Oct 18 '13 at 00:03

1 Answers1

14

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.

ForkandBeard
  • 874
  • 10
  • 28
  • I tried the code and it works for small numbers. I cannot figure out my problem. If I try a test with by filling columns A with random text and some blanks it works. When I have the full data table, it does not. – user1783504 Oct 17 '13 at 15:38
  • As mehow said: What do you mean by 'doesn't work', does it take a long time, do you get an error message? – ForkandBeard Oct 17 '13 at 15:40
  • Actually, it deletes the entire sheet. sorry for not being precise – user1783504 Oct 17 '13 at 15:52
  • @user1783504 I just tested it with 200,000 rows of random data and it worked fine. If you're ending up with no data then I think your 'full data table' must have no values in the first column. – ForkandBeard Oct 17 '13 at 16:11
  • 1
    +1 for an efficient method, autofilter being the other. But *always use Error Handling* with SpecialCells. – brettdj Oct 18 '13 at 00:00
  • This macro fails for me. I changed it to make a certain sheet active and delete a row if the cell in Column C is blank. It works except in the case where I don't have any rows were Column C is blank. Then the macro fails on the line that begins with "Columns". ` Worksheets("Master").Activate Application.ScreenUpdating = False Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Application.ScreenUpdating = True – Mannix Feb 11 '18 at 18:01