repeating a macro for a specified range

[Willip]

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

 

[Tom Ogilvy]

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

 

[Don Guillett]

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

 

[Willip]

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

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?

 

[Gord Dibben]

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

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


Gord Dibben MS Excel MVP