| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete rows accordinng to formula
Hi,
I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
First, delete rows from the higher numbered rows first, or your loop won't
work. For i = 100 to 1 step -1 rows(3*i).delete Shift:=xlUp rows(3*i-1).delete Shift:=xlUp Next "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Sub Macro1 ()
For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n, n + 1).Select Next Selection.Delete Shift:=xlUp End Sub -- Regards, Tom Ogilvy "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
In complete correction. Should be
Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n).Resize(2).Select Selection.Delete Shift:=xlUp Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n, n + 1).Select Next Selection.Delete Shift:=xlUp End Sub -- Regards, Tom Ogilvy "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Thank you very much, Patrick.
My original plan was to select all the rows by the help of a loop and then, I was hoping to delete them all at once. Also, I did not realize that Rows( ) object would not accept something like two variables, as such Rows (n,n+1). Reversing would makes sense! Jack "Patrick Molloy" wrote: First, delete rows from the higher numbered rows first, or your loop won't work. For i = 100 to 1 step -1 rows(3*i).delete Shift:=xlUp rows(3*i-1).delete Shift:=xlUp Next "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Thanks, Tom.
I was not avare of of the fact that you could use "size( )" with Row ( ) object. My original plan was to select all the rows by a loop and then get out of the loop and delete them all at once, rather then reversing and deleting one at a time. Would it be not possible? "Tom Ogilvy" wrote: In complete correction. Should be Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n).Resize(2).Select Selection.Delete Shift:=xlUp Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n, n + 1).Select Next Selection.Delete Shift:=xlUp End Sub -- Regards, Tom Ogilvy "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Range("5:6,9:10,17:18,23:24").Delete Shift:=xlUp
build the text string if n is the row text = text & "," & (n -1) & ":" & n "GreenInIowa" wrote in message ... Thank you very much, Patrick. My original plan was to select all the rows by the help of a loop and then, I was hoping to delete them all at once. Also, I did not realize that Rows( ) object would not accept something like two variables, as such Rows (n,n+1). Reversing would makes sense! Jack "Patrick Molloy" wrote: First, delete rows from the higher numbered rows first, or your loop won't work. For i = 100 to 1 step -1 rows(3*i).delete Shift:=xlUp rows(3*i-1).delete Shift:=xlUp Next "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Thanks, Patrick
"Patrick Molloy" wrote: Range("5:6,9:10,17:18,23:24").Delete Shift:=xlUp build the text string if n is the row text = text & "," & (n -1) & ":" & n "GreenInIowa" wrote in message ... Thank you very much, Patrick. My original plan was to select all the rows by the help of a loop and then, I was hoping to delete them all at once. Also, I did not realize that Rows( ) object would not accept something like two variables, as such Rows (n,n+1). Reversing would makes sense! Jack "Patrick Molloy" wrote: First, delete rows from the higher numbered rows first, or your loop won't work. For i = 100 to 1 step -1 rows(3*i).delete Shift:=xlUp rows(3*i-1).delete Shift:=xlUp Next "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete rows accordinng to formula
Sub Macro1 ()
Dim rng as Range, rng1 as Range For i = 1 To 100 n = 3 * i - 1 set rng = ActiveSheet.Rows(n).Resize(2) if not rng1 is nothing then set rng1 = union(rng1,rng) else set rng1 = rng end if Next rng1.Delete Shift:=xlUp End Sub -- Regards, Tom Ogilvy "GreenInIowa" wrote in message ... Thanks, Tom. I was not avare of of the fact that you could use "size( )" with Row ( ) object. My original plan was to select all the rows by a loop and then get out of the loop and delete them all at once, rather then reversing and deleting one at a time. Would it be not possible? "Tom Ogilvy" wrote: In complete correction. Should be Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n).Resize(2).Select Selection.Delete Shift:=xlUp Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub Macro1 () For i = 100 To 1 Step -1 n = 3 * i - 1 ActiveSheet.Rows(n, n + 1).Select Next Selection.Delete Shift:=xlUp End Sub -- Regards, Tom Ogilvy "GreenInIowa" wrote in message ... Hi, I am trying to delete bunch of empty rows, which are regularly spaced in my worksheet, using "FOR-NEXT" loop. But, it is giving me error. What I my I doing wrong here. Thanks. Sub Macro1 () For i = 1 To 100 n = 3 * i - 1 ActiveSheet.Rows("n, n + 1").Select Next Selection.Delete Shift:=xlUp End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
| If formula result is false, how do I auto-delete that rows? | Excel Worksheet Functions | |||
| How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
| Delete #N/A rows Formula | Excel Programming | |||
Linear Mode
