Search code examples
excelvbadelete-row

Deleting Entire rows where Range Selection is blank (Without looping)


As the title says, It seems obvious but I can't seem to find a one-liner to it without looping. Is there a method able to do this task with a fast execution time ?

Here is my failed attempt (It crashes my excel file) :

Worksheets("Iso_Journal").Range("A:P").EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

Any knowledge would be appreciated !

Edit:

Found this method but it filters instead of deleting (much faster), it does the job but it still not what I truly need

Worksheets("Iso_Journal").Range("A:P").AutoFilter Field:=1, Criteria1:="<>"

Edit 2: (Response to @FaneDuru)

enter image description here


Solution

  • Please, try the next adapted code. If the target is to delete all empty ROWS, it is enough to use a single column. And also to limit the number of rows to be processed:

    Dim wsJ As Worksheet 
    
    Set wsJ = Worksheets("Iso_Journal")
    wsJ.Range("A1:A" & wsj.Range("A" & wsJ.rows.count).End(xlup).row).SpecialCells(xlBlanks).EntireRow.Delete
    

    If the range to be processed is huge, and the workbook contains many formulas, you can make the code a little faster placing Application.Calculation = xlCalculationManual before the deletion line, followed by Application.Calculation = xlCalculationAutomatic. Even if the rows are deleted in block, Excel needs to update the formulas row by row, and no good to calculate after each...