Deleting


Deleting Rows

Rows(4).Delete 
Rows(4:4).Delete

Rows(4:4).Delete Shift:=xlUp
Rows(4:8).Delete Shift:=xlUp

Cells.EntireRow.Delete
Selection.EntireRow.Delete

Deleting Empty Rows

This uses the COUNTA worksheet function to determine if a row is empty.

Sub DeleteEmptyRows() 
Dim lLastRow As Long
Dim lRowNo As Long
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For lRowNo = lLastRow To 1 Step -1
        If Application.WorksheetFunction.CountA(Rows(lRowNo)) = 0 Then
           Rows(lRowNo).Delete
        End If
    Next r
End Sub

Removing any empty/blank rows

?? Don't use For Each .. Next as this will take too long.


Deleting Columns

Cells.EntireColumn.Delete 
Columns("A:D").Delete Shift:=xlLeft


Delete blank rows in a selected range.
Type:=8 argument specifies a Range object; input value must be a range.

Sub DeleteBlankRows() 
Dim rng As Range
Dim selectedRng As Range
Dim iRowCount As Integer
Dim iForCount As Integer

Set selectedRng = Application.Selection
Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)

iRowCount = selectedRng.Rows.Count
For iForCount = iRowCount To 1 Step -1
    If Application.WorksheetFunction.CountA(selectedRng.Rows(iForCount)) = 0 Then
'Delete entire row.
        selectedRng.Rows(iForCount).EntireRow.Delete
'Delete partial row.
'selectedRnd.Rows(iForCount).Delete
    End If
Next
End Sub


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext