Delete Entire Row If No Data Is Found

less than 1 minute read

These piece of code lets you delete an entire row within the used range of the sheet if the entire row contains no data.

' ======================================================================================================
' ## Deletes the entire row within the used range
'    if the ENTIRE row contains no data
'=======================================================================================================
Sub DeleteBlankRows()
    ' Vars
    Dim rngUsedRange As Range
    Dim lngRow As Long

    ' Get the used range on sheet to prepare deleting rows
    Set rngUsedRange = ActiveSheet.UsedRange

    ' Delete entire rows that contain no data
    For lngRow = rngUsedRange.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(rngUsedRange.Rows(lngRow)) = 0 Then
            rngUsedRange.Rows(lngRow).EntireRow.Delete
        End If
    Next lngRow
End Sub