Quick Delete (Excel 97)

Topic

New Reply

How can I just quickly delete all odd rows?

Reply | Quote

Replies

You cannot just quickly delet all odd rows. As soon as you delete the first one, the first even row following it becomes an odd row. If you want to use VBA, you would need to mark the existing odd cells and then run through them again, deleting the ones marked. Try the following

Sub MarkOdd()
Dim rngRows As Range
Set rngRows = Range("A1:A100")
For Each Cell In rngRows
    If Cell.Row Mod 2 = 1 Then
        Cell.Value = "Del"
    End If
Next
For Each Cell In rngRows
    If Cell.Value = "Del" Then
        Cell.EntireRow.Delete
    End If
Next
End Sub

The above deletes all odd rows in the range A1:A100

Andrew C

Reply | Quote

Thanks so much for the quick response

Reply | Quote

Ximon

In a column use this formula and fill it down as many rows as you have data.

=IF(MOD(ROW(),2)0,1/0,””)

For each row this formula will place a #DIV/0! if it is an odd row, and nothing for even.

Then you can use Goto Special and use Formulas Errors and then you get all the rows selected.

Then delete…

Wassim

Reply | Quote

Here is what I would do something like this:

Public Sub DelOdd()
Dim I As Long
    For I = 65534 To 0 Step -2
        Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Delete
    Next I
End Sub

Reply | Quote

Thanks!

Reply | Quote