Solved: Delete every third row

[smecham]

WHat is the Fastest way to delete every third row from the Active Cell?

[Bob Phillips]

Put Y in the adjacent column, let's sya B24 for the example.

In the cell below that, add

=IF(B23="Y","Y","")


Add a filter to column B, Data>Filter>Autofilter, and select Y from the dropdown, and delet the rows with a visible Y.

[Erdin? E. Ka]

WHat is the Fastest way to delete every third row from the Active Cell?

Hi smecham,

I do not want you break your head, check this codes below;

[vba]
Sub Example_Of_A_For_Next_Loop()
Dim i As Byte
Dim j As Byte

j = 3
For i = 1 To Int(20 / j)
Cells(i + j, 1).Select
Selection.Delete Shift:=xlUp
j = j + 3
Next i

[A1].Select

MsgBox "Every Third Row Is Deleted", vbInformation
End Sub
[/vba]

[johnske]

[VBA]
Option Explicit

Sub Delete3rdRows()
Dim N As Long
Application.ScreenUpdating = False
For N = 3 To Cells.SpecialCells(xlCellTypeLastCell).Row Step 2
Rows(N).Delete
Next
Application.ScreenUpdating = True
End Sub
[/VBA]

[Jacob Hilderbrand]

When deleting a bunch of rows, it is a lot faster to delete them all at once.

[VBA]
Option Explicit

Sub Delete3rdRows()

Dim i As Long
Dim LastRow As Long
Dim DelRange As Range

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set DelRange = ActiveCell
For i = ActiveCell.Row + 3 To LastRow Step 3
Set DelRange = Union(DelRange, Range("A" & i))
Next i
DelRange.EntireRow.Delete

End Sub

[/VBA]

[smecham]

Thanks for all the Solutions. thanks tried them all. Since I have a potential of 25000 rows to rip through. DRJ's solution seems to fit the best. Thanks again for all the responses.