repeating a macro for a specified range
 

Hi

Im sure someone can help me with this. I have created an input box for
some data pasted in from another program. Some of these lines are
headers and page breaks which I want to remove , I have got an if
statement to flag each line with a Y if it needs deleting and for one
line I can use

If Range("L6") = "Y" Then

Range("E7:E28").Select
Range("E7:E28").Cut Destination:=Range("E6:E27")

End If

the next one would be

If Range("L7") = "Y" Then

Range("E8:E28").Select
Range("E8:E28").Cut Destination:=Range("E7:E27")


End If

etc


Is there a way of getting the macro to run automatically for all 20
lines, rather then just having 20 or so of the above

thanks in advance Will

repeating a macro for a specified range
 

If it is OK to delete the entire row, then

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
rows(i).Delete
end if
Next
end Sub

If just the cell in column E

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
cells(i,"E").Delete Shift:=xlShiftUp
end if
Next
end Sub


--
Regards,
Tom Ogilvy

"Willip" wrote in message
ups.com...
Hi

Im sure someone can help me with this. I have created an input box for
some data pasted in from another program. Some of these lines are
headers and page breaks which I want to remove , I have got an if
statement to flag each line with a Y if it needs deleting and for one
line I can use

If Range("L6") = "Y" Then

Range("E7:E28").Select
Range("E7:E28").Cut Destination:=Range("E6:E27")

End If

the next one would be

If Range("L7") = "Y" Then

Range("E8:E28").Select
Range("E8:E28").Cut Destination:=Range("E7:E27")


End If

etc


Is there a way of getting the macro to run automatically for all 20
lines, rather then just having 20 or so of the above

thanks in advance Will

repeating a macro for a specified range
 

something like this to play with.

for i=7 to 10
if ucase(cells(i,"l"))="Y" then _
range(cells(i,"e"),cells(i+21,"e")).cut destination:=cells(i-1,"e")
next i

--
Don Guillett
SalesAid Software

"Willip" wrote in message
ups.com...
Hi

Im sure someone can help me with this. I have created an input box for
some data pasted in from another program. Some of these lines are
headers and page breaks which I want to remove , I have got an if
statement to flag each line with a Y if it needs deleting and for one
line I can use

If Range("L6") = "Y" Then

Range("E7:E28").Select
Range("E7:E28").Cut Destination:=Range("E6:E27")

End If

the next one would be

If Range("L7") = "Y" Then

Range("E8:E28").Select
Range("E8:E28").Cut Destination:=Range("E7:E27")


End If

etc


Is there a way of getting the macro to run automatically for all 20
lines, rather then just having 20 or so of the above

thanks in advance Will

repeating a macro for a specified range
 

Tom Ogilvy wrote:

If it is OK to delete the entire row, then

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
rows(i).Delete
end if
Next
end Sub

If just the cell in column E

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
cells(i,"E").Delete Shift:=xlShiftUp
end if
Next
end Sub


--
Regards,
Tom Ogilvy

"Willip" wrote in message
ups.com...
Hi

Im sure someone can help me with this. I have created an input box for
some data pasted in from another program. Some of these lines are
headers and page breaks which I want to remove , I have got an if
statement to flag each line with a Y if it needs deleting and for one
line I can use

If Range("L6") = "Y" Then

Range("E7:E28").Select
Range("E7:E28").Cut Destination:=Range("E6:E27")

End If

the next one would be

If Range("L7") = "Y" Then

Range("E8:E28").Select
Range("E8:E28").Cut Destination:=Range("E7:E27")


End If

etc


Is there a way of getting the macro to run automatically for all 20
lines, rather then just having 20 or so of the above

thanks in advance Will


That second one looks like what I'm after, I tried pasting it in, and
it gives a compile error, object required, with "lastrow =" highlighted
on the line set lastrow = cells(rows.count,"L").End(xlup).row

any ideas what I need to do to fix it?

repeating a macro for a specified range
 

Remove the "Set" from the line to read..........

lastrow = Cells(Rows.Count, "L").End(xlUp).Row


Gord Dibben MS Excel MVP

On 24 Sep 2006 09:47:33 -0700, "Willip" wrote:


Tom Ogilvy wrote:

If it is OK to delete the entire row, then

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
rows(i).Delete
end if
Next
end Sub

If just the cell in column E

Sub ABC()
Dim i as Long, lastrow as Long
set lastrow = cells(rows.count,"L").End(xlup).row
for i = lastrow to 6 step -1
if cells(i,"L") = "Y" then
cells(i,"E").Delete Shift:=xlShiftUp
end if
Next
end Sub


--
Regards,
Tom Ogilvy

"Willip" wrote in message
ups.com...
Hi

Im sure someone can help me with this. I have created an input box for
some data pasted in from another program. Some of these lines are
headers and page breaks which I want to remove , I have got an if
statement to flag each line with a Y if it needs deleting and for one
line I can use

If Range("L6") = "Y" Then

Range("E7:E28").Select
Range("E7:E28").Cut Destination:=Range("E6:E27")

End If

the next one would be

If Range("L7") = "Y" Then

Range("E8:E28").Select
Range("E8:E28").Cut Destination:=Range("E7:E27")


End If

etc


Is there a way of getting the macro to run automatically for all 20
lines, rather then just having 20 or so of the above

thanks in advance Will


That second one looks like what I'm after, I tried pasting it in, and
it gives a compile error, object required, with "lastrow =" highlighted
on the line set lastrow = cells(rows.count,"L").End(xlup).row

any ideas what I need to do to fix it?