Deleting every other row

[nir020]

I am trying to write a macro that deletes every other row in spreadsheet, I
have written the following code

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/11/2006 by readn
'

'
Dim M As Range
Dim n As Integer

n = 9
Set M = Range("n.value:n.value")


For a = 1 To 150


M.Select
Selection.Delete Shift:=xlUp
n=n+2

Next a

End Sub

But I cannot get to work, any suggestions?

 

[raymond.allan]

Try doing it in reverse

For a = 150 to 1 step -1

Raymond

 

[Bob Phillips]

Sub Macro1()
Dim a As Long

Application.ScreenUpdating = False
For a = 150 To 2 Step -2
Rows(a).Delete
Next a
Application.ScreenUpdating = True

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Ramage]

Bob's reply gives you the answer, put there are several problems with your
code for which you may find comments useful.

The first is the way you have attempted to set a range variable:
n = 9
Set M = Range("n.value:n.value")

Here, n is an integer varaible, but because you have included it in the
quotes, VBA cannot see it as a variable- just text. Also, you do not need the
..value. To use an integer variable to set a range object variable it is
better to use one of these:
Set M = Range("A" & n) 'uses & to concatenate text with the variable to
give "A9"
Set M = Cells(n,2) ' Row 9, column 2

Hope this helps,
Dave