Insert\Delete Row or columns and replace with Data

     

  • Such a type of small tricks plays vital role in major programs

    •  

     

     

     

    Insert Row based on condition

     

  • We can insert one new row based on condition

    •  

    Private Sub CommandButton1_Click()
    Do Until ActiveCell.Value = ""
    'place the cursor in A2 cell to execute the query
    If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then
    ActiveCell.EntireRow.Insert
    ActiveCell.Offset(2, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
    End Sub

     

     

    Insert new Row based on condition

     

    Private Sub CommandButton1_Click()
    Dim j As Long
    j = 2
    Do Until Cells(j, 1).Value = ""
    If Cells(j, 1).Value <> Cells(j, 1).Offset(-1, 0).Value Then
    Cells(j, 1).EntireRow.Insert
    j = j + 2
    Else
    Cells(j, 1).Offset(1, 0).Select
    j = j + 1
    End If
    Loop
    End Sub

     

     

     

    Insert multiple rows

     

    Private Sub CommandButton1_Click()
    Dim rws As Integer
    rws = 4
    For i = 1 To rws
    'i want to insert 4 rows from B1
    Range("B1").EntireRow.Insert
    Next
    End Sub
    Private Sub CommandButton2_Click()
    Dim rws As Integer
    rws = 4
    For i = 1 To rws
    'i want to delete 4 rows from B1
    Range("B1").EntireRow.Delete
    Next
    End Sub

     

     

    Insert one row and one column

     

    Private Sub CommandButton1_Click()
    Range("A1").EntireRow.Insert
    Range("A1").EntireColumn.Insert
    End Sub
    Private Sub CommandButton2_Click()
    Range("A1").EntireColumn.Delete
    Range("A1").EntireRow.Delete
    End Sub

     

     

    Insert\Delete Multiple columns

     

    Private Sub CommandButton1_Click()
    Range("A1:A5").Rows.Select
    Selection.EntireRow.Insert
    Columns("B:C").Select
    Selection.EntireColumn.Insert
    End Sub
    Private Sub CommandButton2_Click()
    Range("A1:A5").Rows.Select
    Selection.EntireRow.Delete
    Columns("B:C").Select
    Selection.EntireColumn.Delete
    End Sub

     

    Delete entire row \ column

     

    Private Sub CommandButton1_Click()
    Rows(1).Select
    Selection.EntireRow.Delete
    Range("A1").EntireRow.Delete
    Range("A1").EntireColumn.Delete
    Range("A1:A15").EntireRow.Delete
    Columns("A:C").EntireColumn.Delete
    End Sub

     

     

    Insert Cell

     

    Private Sub CommandButton1_Click()
    'insert cells = insert rows
     Range("e4").Cells.Insert
    End Sub

     

     

     

     

    Insert Rows and Columns - shortCut

     

    Private Sub CommandButton1_Click()
    'One row down from A1 cell
     Range("A1").Insert shift:=xlShiftDown
    'Five rows down from A1 cell
     Range("A1:A5").Insert shift:=xlShiftDown
    'Move 3 columns to right
     Range("A:C").Insert shift:=xlShiftToRight
    End Sub

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli