Macro to delete 2 rows every 500 rows
 

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!

Macro to delete 2 rows every 500 rows
 

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)



wrote in message
...
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!

Macro to delete 2 rows every 500 rows
 

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


" wrote:

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!

Macro to delete 2 rows every 500 rows
 

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)



"Joel" wrote in message
...
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


" wrote:

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!