Repeating Macro

[Dino]

Is there a way to have a macro repeat a specified number of times? For
example, I have a spreadsheet that comes from my accounting software that
has about 30,000 lines. Only the first, fourth, seventh etc line is needed.
From the beginning, I need to move down a row, delete two rows, move down a
row, delete two rows until the entire data is cleaned up. I can record the
macro to delete the two lines, but I don't know how to make it repeat.
Ideas?

Thanks,
Dino

 

[Bernie Deitrick]

Dino,

Looping in this case would be very, very, very slow. Better to sort based on a helper column, and
do one deletion, as done in the macro below.

HTH,
Bernie
MS Excel MVP


Sub KeepEveryThirdRow()
Dim myRows As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

Range("A1").EntireColumn.Insert
ActiveCell.FormulaR1C1 = "=MOD(ROW(),3)"

myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1:A" & myRows).FormulaR1C1 = _
"=IF(MOD(ROW(),3)=1, " & _
"""Keep"",""Trash"")"
Application.Calculate
With Range("A:A")
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, header:=xlNo
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[Norman Jones]

Hi Dino,

See responses to your similar question posted two days ago:

http://tinyurl.com/drcet

 

[Dana DeLouis]

Only the first, fourth, seventh etc line is needed. ...but I don't know
Here's a outline of something I like to use...

Sub Demo()
'// Dana DeLouis
Dim R, C
ActiveSheet.UsedRange
With Cells.SpecialCells(xlCellTypeLastCell)
R = .Row
C = .Column + 1
End With
Cells(1, C) = "x"
Range(Cells(1, C), Cells(3, C)).AutoFill Range(Cells(1, C), Cells(R, C))
Cells(1, C).EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
[A1].Select
End Sub