repeating a macro for a specified range

  • Thread starter Thread starter Willip
  • Start date Start date
W

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
 
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
 
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
 
Tom said:
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
Click to expand...

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?
 
Remove the "Set" from the line to read..........

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


Gord Dibben MS Excel MVP
 
Back
Top