How do delete every second Row

[John]

What code would I use to delete every second Row starting at Row 10 i.e.
delete Row10; 12; 14 etc?

Thanks

 

[Andy Wiggins]

The trick is to start at the bottom and work your way up the dataset.

Sub DeleteRowDemo()
Dim x
For x = 14 To 2 Step -2
Rows(x & ":" & x).Delete
Next
End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Samura]

This is an example.


Sub DeleteRows()

'selecting the rows 10, 12, 14
Range("10:10,12:12,14:14").Select

'deleting the selecetd rows
Selection.Delete Shift:=xlUp

End Sub

 

[John]

Thanks Guys

The trick is to start at the bottom and work your way up the dataset.

Sub DeleteRowDemo()
Dim x
For x = 14 To 2 Step -2
Rows(x & ":" & x).Delete
Next
End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Bob Phillips]

Here is an alternative way. Just set rng to your target range

Sub DeleteRows()
Dim rng As Range

Range("A1").EntireColumn.Insert
Set rng = Range("A10:A100")
Range("A10").FormulaR1C1 = "=MOD(ROW(),2)"
Range("A10").AutoFill Destination:=rng, Type:=xlFillDefault
Columns("A:A").AutoFilter Field:=1, Criteria1:="0"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Columns("A:A").EntireColumn.Delete
End Sub

--

HTH

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

 

[Nick Hodge]

John

Just for fun, without code

=IF(MOD(ROW(A10),2)=0,"x","")

Enter this formulae into a blank cell in the 10th row and copy down as far
as necessary. Filter the rows by the 'x' in every other row and press
delete. Sort the rows and the blanks will be at the bottom.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Bob Phillips]

Same approach I took, but I added it in code

--

HTH

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

 

[Earl Kiosterud]

Like we need another way to do this. This goes forward through the rows:


Set MyRange = Range("A9")
Do While MyRange <> ""
MyRange.Offset(1, 0).EntireRow.Delete
Set MyRange = MyRange.Offset(1, 0)
Loop