How do delete every second Row

J

John

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

Thanks
 
A

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"
 
S

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
 
J

John

Thanks Guys

Andy Wiggins said:
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"
Click to expand...
 
B

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)
 
N

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
[email protected]
 
B

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)
 
E

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
 
Top