MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting every other row


Posted by Lisa on October 23, 2001 6:26 AM

Can anyone help me with the following?

I have a range of data and I want to delete every other row. Is there an easy way to do it?

Thanks in advance----


Posted by Mark W. on October 23, 2001 6:35 AM

Lisa, you could use Excel's Advanced AutoFilter with
a computed criteria of =MOD(ROW(A2),2)=0 to display
only even numbered rows which could then be selected
and deleted. Or use =MOD(ROW(A2),3)=0 to similarily
display odd numbered rows.

Posted by EDDIE G on October 24, 2001 8:03 AM

THIS WILL DELETE EVERY OTHER ROW STARTING AT ROW 7 OR WHATEVER CELL STARTING POINT YOU WANT. IT WORKS WELL.

EDDIE

Sub DELETE_DATA_ODD_ROWS_OVERALL_REPORT()
Dim rng As Range
Application.ScreenUpdating = False
Columns("A:A").Insert
Set rng = Range("A7:A999")
With rng
.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,1,"""")"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.ClearContents
End With
Columns("A:A").Delete
Application.ScreenUpdating = True
End Sub


Posted by EDDIE G on October 24, 2001 8:06 AM


THIS WILL DELETE ALL EMPTY ROWS

Sub DELETE_EMPTY_ROWS()
LASTROW = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For R = LASTROW To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub