Delete/Remove Blank/Empty Rows/Columns


Delete or Remove Blank Rows
Description: Use this macro to delete/remove empty/blank rows with in the Selection.
Code: Sub DeleteBlankRows()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim Rw As Range   

    If WorksheetFunction.CountA(Selection) = 0 Then
       MsgBox "No data found", vbOKOnly
       Exit Sub
    End If
        Selection.SpecialCells(xlCellTypeBlanks).Select
        For Each Rw In Selection.Rows
            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                Selection.EntireRow.Delete
            End If
        Next Rw

End Sub


Delete or Remove Empty Columns
Description: Use this macro to delete/remove empty/blank columns with in the Selection.
Code: Sub DelBlankColumns()
' Deletes all empty columns on the active worksheet
Dim iCol As Integer
    With ActiveSheet.UsedRange
        For iCol = .Column + .Columns.count - 1 To 1 Step -1
        If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
            If Cells(65536, iCol).End(xlUp).Row = 1 Then
                Columns(iCol).Delete
            End If
        End If
        Next iCol
    End With

End Sub


Comments

Anonymous said…
Wrong macro check it again