PDA

View Full Version : Solved: Delete every third row



smecham
09-25-2006, 02:46 PM
WHat is the Fastest way to delete every third row from the Active Cell?:banghead:

Bob Phillips
09-25-2006, 03:27 PM
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
09-25-2006, 03:33 PM
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
09-25-2006, 04:22 PM
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
09-25-2006, 09:04 PM
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
09-26-2006, 07:30 AM
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. :)