Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |