deleting variable number of rows
 

I hava a sheet with more than 1000 rows and the problem is that only
every 13th row contains information I want to have. So I want to
delete the rest of the rows. I have been trying this with

Sub Macro1()

Rows("10:23").Select
Selection.Delete Shift:=xlUp
End Sub

and it works fine but I want to do this automatically. Is there a way
to make the row selection (Rows("10:23") automatical? I tried this
with a counter like
Rows(a & ":" & b) with a and b being values of the counter but it
doesn't work. Any sugestions?

deleting variable number of rows
 

Try this on a test workbook

Sub test1()
Application.ScreenUpdating = False
Dim myRows As Long
Range("A1").EntireColumn.Insert
myRows = ActiveSheet.UsedRange.Rows.Count

With Range(Cells(1, 1), Cells(myRows, 1))
.FormulaR1C1 = "=IF(MOD(ROW(),13)=1,""Keep"","""")"
.Value = .Value
End With
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
Range("A1").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Nick" wrote in message om...
I hava a sheet with more than 1000 rows and the problem is that only
every 13th row contains information I want to have. So I want to
delete the rest of the rows. I have been trying this with

Sub Macro1()

Rows("10:23").Select
Selection.Delete Shift:=xlUp
End Sub

and it works fine but I want to do this automatically. Is there a way
to make the row selection (Rows("10:23") automatical? I tried this
with a counter like
Rows(a & ":" & b) with a and b being values of the counter but it
doesn't work. Any sugestions?