Repeating Macro

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

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



"Dino" <[email protected]> wrote in message
news:eDQijE%[email protected]...
> 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
>
>
>
>

Hi Dino,

See responses to your similar question posted two days ago:

http://tinyurl.com/drcet


---
Regards,
Norman



"Dino" <[email protected]> wrote in message
news:eDQijE%[email protected]...
> 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
>
>
>
>

>> Only the first, fourth, seventh etc line is needed. ...but I don't know
>> how to make it repeat. Ideas?

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:OQAJiS%[email protected]...
> 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
>
>
>
> "Dino" <[email protected]> wrote in message
> news:eDQijE%[email protected]...
>> 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
>>
>>
>>
>>
>
>