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 Columns
Cells.EntireColumn.Delete
Columns("A:D").Delete Shift:=xlLeft
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
Deleting 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
Deleting All Visible Rows
This deletes all the visible rows between 1 and 100.
ActiveSheet.Range(Cells(1, 3), Cells(100, 3)).SpecialCells(xlCellType.xlCellTypeVisible).EntireRow.Delete
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext