deleting variable number of rows

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

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?
 
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).EntireRow.Delete
Range("A1").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub
 
Back
Top