vbscript to delete rows in an excel sheet using a specific condition

Question

I have an excel sheet with a column containing data as follows and there is data in other columns.

Here there are blank cells between the rows. Between 1014,1027 there is 1 blank Cell. There are 3 blank Cells between 1027 and other 1027 and so on..

Script should delete the rows with more than one blank cell in "column A" between the rows that contains data in "column A".

Eg: Script should delete the rows 1027(starting row always has a value) and next three empty rows, so it should not delete the rows if the gap between two non-empty values is 1. The same process should be done for the entire sheet.

There are more than one empty cell between the following values.

Code

1014

1027

1033

1020

1033

1008

Please suggest me on this.

Gary's Student Gary's Student · Accepted Answer · 2013-10-03T17:24:40

Give this a try:

Sub rowKiller()
    Dim N As Long, i As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To N - 1
        If Cells(i - 1, 1) <> "" And Cells(i, 1) = "" And Cells(i + 1, 1) <> "" Then
            Cells(i, 1).Value = "XXX"
        End If
    Next i

    Range("A:A").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Range("A:A").Replace "XXX", ""

End Sub

vbscript to delete rows in an excel sheet using a specific condition

1 Answers