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

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

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

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

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

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

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

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

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