Delete every third row

[smecham]

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

[Bob Phillips]

Put Y in the adjacent column, let's say 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 delete the rows with a visible Y.

[Erdin? E. Ka]

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

Hi smecham,:hi:

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



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

[johnske]

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

[Jacob Hilderbrand]

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



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

[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. :)