Deleting every other row
 

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?

Deleting every other row
 

Try doing it in reverse

For a = 150 to 1 step -1

Raymond

nir020 wrote:
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?

Deleting every other row
 

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)

"nir020" wrote in message
...
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?

Deleting every other row
 

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

"nir020" wrote:

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?