deleting every second row

[Guest]

I have several thousand rows and need to delete every second and third row based on no other criteria- just by second and third row
More info.... I need to import an old dataset into a data analysis program. It is a three card format. I only need the first row and the second and third are screwing everything up.

 

[Bob Phillips]

Shawn,

Here's a solution. Make sure you save your data before running

Sub DeleteRows()
Dim cLastRow As Long
Dim i As Long

With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If cLastRow Mod 3 = 1 Then
cLastRow = cLastRow + 2
ElseIf cLastRow Mod 3 = 2 Then
cLastRow = cLastRow + 1
End If

For i = cLastRow To 1 Step -3
.Cells(i, 1).EntireRow.Delete
.Cells(i - 1, 1).EntireRow.Delete
Next i
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have several thousand rows and need to delete every second and third row

based on no other criteria- just by second and third row.

More info.... I need to import an old dataset into a data analysis

program. It is a three card format. I only need the first row and the second
and third are screwing everything up.

 

[NICK]

Hi,

there are certainly many ways you could do this, but the
easiest might be to use =OFFSET function. If you data is
in Column A and you want to get rid of each 2nd and 3rd
row, then use the following formula.

=OFFSET($A$1,D1,0)

then in A1 you have your data starting and in D1 you have
it going down 0, 3, 6, 9 etc...

this should do the job

 

[J.E. McGimpsey]

one way:

Public Sub Del23()
Dim i As Long
Dim rDelete As Range
Set rDelete = Range("A2").Resize(2, 1)
For i = 5 To Range("A" & Rows.Count).End(xlUp).Row Step 3
Set rDelete = Union(rDelete, Cells(i, 1).Resize(2, 1))
Next i
rDelete.EntireRow.Delete
End Sub

 

[Guest]

if you know some macro programming yuo can paste this into the VBA window ALT-F11, and run it as a macro. Before starting the macro put select the first line that you want to keep

If the macro doesn't delete enough lines start it up once more. The macro deletes 100 times 2 lines.

when doing such procedures, always keep a backup of all your data.

Here is the macro to paste

Sub Makro2(
For i = 1 To 10
ActiveCell.Offset(1, 0).Rows("1:2").EntireRow.Selec
Selection.Delete Shift:=xlU
Next

End Su

hope this will help you

Torstein

 

[Jordon]

I have several thousand rows and need to delete every second
and third row based on no other criteria- just by second and third row.

You could insert a new column A and enter 1 in A1, 2 in A2 and 3 in A3.
Copy those all the way down and sort the whole thing on column A. The
2's and 3's will be at the bottom.

If you require that the result be in it's original order then insert a
second
column and number these sequentially from the top to the bottom. After
the first sort on A, sort what's left on B.

Jordon

 

[Dana DeLouis]

If you don't like loops, here is just one of a few ways:

Sub Demo()
'// Dana DeLouis
Dim Remember As Boolean

Remember = Application.ScreenUpdating
Application.ScreenUpdating = False

Columns(1).Insert
With Range(Cells(1), Cells.SpecialCells(xlCellTypeLastCell))
[A1] = "#"
[A1:A3].AutoFill Destination:=.Columns(1)
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns(1).Delete
End With
ActiveSheet.UsedRange

Application.ScreenUpdating = Remember
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

I have several thousand rows and need to delete every second and third row

based on no other criteria- just by second and third row.

More info.... I need to import an old dataset into a data analysis

program. It is a three card format. I only need the first row and the second
and third are screwing everything up.