Macro to delete 2 rows every 500 rows

[beginner1.mat]

I have a bunch of files that I would like to delete 2 rows every
99rows (basically there is information I don't need every 99rows after
the first 100 rows...or 101 with the headers). I have "recorded" a
macro for this, but I thought there would be a better way, as the
number of rows is different for each file. Is there a way to do this,
since I don't have a constant number of rows in each sheet?

Rows("102:103").Select
Selection.Delete Shift:=xlUp
Rows("201:202").Select
Selection.Delete Shift:=xlUp

etc....

As I said, I recorded this manually, but there has to be a better way!

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
If LastRow Mod 100 <> 2 Then

LastRow = LastRow - LastRow Mod 100 + 2
End If
For i = LastRow To 1 Step -100

.Rows(i).Resize(2, 1).Delete
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

For rows you don't need the shift up it is automatic.

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = 102
Do While RowCount <= LastRow
Rows(RowCount & ":" & (RowCount + 1)).Delete
RowCount = RowCount + 99
Loop

 

[Bob Phillips]

You should be subtracting 98 in this method.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)