How to delete a blank row with all empty cell and keep others row that have some empty cell?-CodePudding

I have a mircosoft office excel file like that,

when I use the method: Find special-> blank it search all row, but I only want to delete all blank row, how to realize it?

CodePudding user response：

Throw this into a module in VBA, select your range of cells you want to process and then run the macro ...

Public Sub DeleteBlankRows()
    Dim objRow As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    On Error GoTo ExitGracefully
    
    With Selection
        For lngRow = .Rows.Count To 1 Step -1
            For Each objRow In .Rows(lngRow)
                If WorksheetFunction.CountBlank(objRow) = objRow.Cells.Count Then
                    objRow.Delete xlShiftUp
                End If
            Next
        Next
    End With
    
ExitGracefully:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

... it will process each row individually and determine if the amount of cells that are blank match the total amount of cells in the row that you have selected. If they match, it will shift all cells up.

If you want to delete the entire row across the worksheet, change this line ..