Excel VBA: Code To Delete Row IF Blank Cell; Optimization

Question

Essentially, when running the below code within one workbook (1 sheet) it completes within an instant. But when using it in my main workbook (couple of sheets, barely any data) it takes a while to complete. How can I optimize the below code?

Sub DeleteBlankRows()
    On Error Resume Next
    Sheets("Sheet4").Activate
    Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Remove the `On Error Resume Next` to see if there are any errors popping up that could be breaking things. `OERN` is useful in very, _very_ few, very specific instances. It's not meant to be a "catch-all, sweep every error in the universe under the carpet and pretend it didn't happen magic wand" to make your code _appear_ to work. Not saying that you're hitting an error, just that this probably isn't what you intended for this bit of code. — FreeMan, Sep 06 '18 at 14:47

BruceWayne · Answer 1 · 2018-09-06T14:49:20.410

3

Try avoiding the use of an entire column, as well as .Activate:

Sub DeleteBlankRows()
   ' On Error Resume Next
    Dim lastRow As Long
    With Sheets("Sheet4")
        lastRow = .Cells(Rows.Count, 4).End(xlUp).row
        .Range(.Cells(1, 4), .Cells(lastRow, 4)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Edit: Commented out the On Error Resume Next

edited Sep 06 '18 at 14:49

answered Sep 06 '18 at 14:25

BruceWayne

22,923
15
65
110

But I recommend to find the last used row by another column than "C" because this column obviously has blank cells and they might be in the end. So better use `.Cells(Rows.Count, 1)` or some other column that has no blanks. Otherwise it might miss some rows in the end. – Pᴇʜ Sep 06 '18 at 14:29
1

@Pᴇʜ - Good point, I'll let OP make any changes or let us know what column would be better. – BruceWayne Sep 06 '18 at 14:30
Thanks BruceWayne & Pᴇʜ ; unfortunately there must be something else causing the Workbook to lag out when executing this isolated code, as the above had no effect. Ah well... – Jalkey Sep 06 '18 at 14:42
1

@Jalkey go through it step-by-step with F8. If the row with `.Delete` takes much time you cannot optimize anymore. But if any events are running afterwards then check the code of these events. – Pᴇʜ Sep 06 '18 at 14:48
But there must be some way... when on a fresh workbook, it's efficient - there's something that's bogging it down just be being a bigger spreadsheet (few sheets, couple hundred rows around the place). Will probably do this on the initial sheet first, then copy... mm. – Jalkey Sep 06 '18 at 16:23
@Jalkey - What kind of data do you have in that worksheet? It *might* be faster to instead simply filter out the blanks (meaning, add a filter and select the option which shows you only those entries with a blank) and then simply delete the visible rows. – BruceWayne Sep 06 '18 at 16:36
It's just a bunch of numbers, with some words - nothing complex or long. I was avoiding filtering because I'm running a strain of codes to essentially re-distribute another workbook's information (hence the vba approach). This Sub is lagging when being ran by itself, fyi. – Jalkey Sep 07 '18 at 09:16
@Jalkey how many rows does the sheet have? (What is`lastRow`?) – BruceWayne Sep 07 '18 at 11:27

Luis Curado · Answer 2 · 2018-09-06T15:08:23.807

0

you could try too to stop the automatic calculation and screen update and at the end reenable all.

try this and test too with the other codes

Sub DeleteBlankRows()
   Application.ScreenUpdating = False
   Application.Calculation = xlManual

   On Error Resume Next
   Sheets("Sheet4").Activate
   Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

   Application.ScreenUpdating = true
   Application.Calculation = xlAutomatic
End Sub

Good Luck

edited Sep 06 '18 at 15:08

answered Sep 06 '18 at 14:35

Luis Curado

724
5
16

Thanks Luis; unfortunately there must be something else causing the Workbook to lag out when executing this isolated code, as the above had no effect. Ah well... – Jalkey Sep 06 '18 at 14:42
1

@luis things like `.ScreenUpdating` have no effect on code that does one action only, because this one action has to be rendered either directly when this action happens `.Delete` or latest when `.ScreenUpdating = true`. So this is useless in this case here. – Pᴇʜ Sep 06 '18 at 14:45
sorry, ive made a mistake. i want to put Application.Calculation = xlManual. ive updated – Luis Curado Sep 06 '18 at 15:32
Sadly, still not able to mitigate the lagout to I experience. Thanks again though. – Jalkey Sep 06 '18 at 16:01

score -2 · Answer 3 · answered Sep 06 '18 at 16:06

-2

   lastRow = .Cells(Rows.Count, 4).End(xlUp).row

I never use this method for figuring out last row. It takes too long... Basically processing every cell starting from the bottom of the worksheet. Instead, I count the number of cells with values. I use that number to run a for loop which tests to see if there is a value in a given cell and counts until all cells with values are accounted for. Code wise, its more complicated... but in my experience executes more quickly.

kount = Application.WorksheetFunction.CountA(krng) 'Count how many used cells there are

kRow = 1
j = 1

Do Until j = kount + 1 'Do until all used cells are acounted for

If Cells(kRow, l).Value = vbNullString Then 'If current cell is empty skip it
    Else
    j = j + 1 'If the current cell has a value count up
    End If
    kRow = kRow + 1 'but go on to the next row either way
Loop

Where kRow is the last row with a value

answered Sep 06 '18 at 16:06

Orin

1
1

1

*It takes too long... Basically processing every cell starting from the bottom of the worksheet* - that is not what that method does, at all. `Range.End` is an instantaneous jump to the end of the range, i.e. when you keyboard-navigate with Ctrl+Arrow keys: it's instantaneous, and is *the* single [most reliable way of finding the last row in a column](https://stackoverflow.com/a/11169920/1188513). That loop is unbelievably slower than the `Range.End` way, since it accesses the worksheet cells *once per iteration*. – Mathieu Guindon Sep 06 '18 at 16:10
1

That loop will also blow up with a *type mismatch* error for any `Cell(kRow, 1).Value` that contains a cell error, such as `#N/A`. Not to mention it's implicitly working off `ActiveSheet` (or `Me`, if it's written in a worksheet's code-behind), and will return a misleading result if `krng` isn't on the active sheet. – Mathieu Guindon Sep 06 '18 at 16:14

Excel VBA: Code To Delete Row IF Blank Cell; Optimization

3 Answers3