Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Macro
Dino: Are the rows blank? If, for instance, column B is always blank in the
first, fourth, seventh, then you can: Select column B. Hit Edit--Go To--Special, Blank cells, Ok. Hit Edit--Delete. Choose Entire Rows. OK. ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Dino" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Macro
Hi Dino.
Try: '============= Public Sub TesterA001() Dim WB As Workbook Dim SH As Worksheet Dim LRow As Long Dim i As Long Dim CalcMode As Long Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With LRow = Cells(Rows.Count, "A").End(xlUp).Row Select Case LRow Mod 3 Case 0: LRow = LRow - 1 Case 1: LRow = LRow - 2 End Select For i = LRow To 1 Step -3 Rows(i).Resize(2, 1).Delete Next i XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<============= --- Regards, Norman "Dino" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Macro
Hi Dino,
Change: Rows(i).Resize(2, 1).Delete to Rows(i).Resize(2).Delete --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeating a Macro For Each Selected Worksheet | Excel Worksheet Functions | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro to eliminate repeating account numbers | New Users to Excel | |||
help with macro to save repeating data entry | Excel Discussion (Misc queries) |