Deleting Rows


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


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
        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

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.
'Delete partial row.
    End If
End Sub

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