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
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
.Cells(Rows.Count, 1) or some other column that has no blanks. Otherwise it might miss some rows in the end.
.Delete takes much time you cannot optimize anymore. But if any events are running afterwards then check the code of these events.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
.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.
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
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. That loop is unbelievably slower than the Range.End way, since it accesses the worksheet cells once per iteration.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.
On Error Resume Nextto see if there are any errors popping up that could be breaking things.OERNis 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.