Inserting


Inserting Rows

Selection.EntireRow.Insert 

Inserting Columns

Sheet1.Range("A:A").EntireColumn.insert 
Sheet1.Range("A:A").Insert

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