How you decimate data using EXCEl (i.e., remove every second or t.

[Guest]

I have a data file (over 9000 rows and 10 columns worth of data), and I would
like to remove every fifth row to reduce the amount of data. How do i
accomplish this?

 

[Bernie Deitrick]

Blue,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub RemoveEveryFifthRow()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(MOD(ROW(),5)=0,""Trash"",""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

 

[Guest]

One way:


Sub DeleteEveryFifthRow()
Dim lRow As Long
lRow = Range("A65536").End(xlUp).Row
Do While lRow > 0
Cells(lRow, 1).EntireRow.Delete
lRow = lRow - 5
Loop
End Sub