Tuesday, March 25, 2014

Insert & Delete Entire Row in VBA



Option Explicit
Dim rowcount As Long, myrng As Range
Sub removeBlankcells()

rowcount = ThisWorkbook.Sheets(1).Range("A65500").End(xlUp).Row
    Set myrng = ThisWorkbook.Sheets(1).Range("A2:A" & rowcount)
     ThisWorkbook.Sheets(1).UsedRange.AutoFilter field:=1, Criteria1:=""
     ThisWorkbook.Sheets(1).Range("A1:A" & rowcount).SpecialCells(xlCellTypeBlanks).Select
     Selection.EntireRow.Delete
    ThisWorkbook.Sheets(1).AutoFilterMode = False
End Sub



Option Explicit
Dim rowcount As Long, i As Integer
Sub insertRow()
Application.ScreenUpdating = False
    rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
    For i = 2 To (rowcount - 1) * 2
        ThisWorkbook.Sheets(1).Range("A" & (i + 1)).EntireRow.Insert
          i = i + 1
       Next

End Sub





File to downlaod

No comments:

Post a Comment