Macro
 

Does anyone have some ideas on how to do this? I'm trying to write a macro
that will look at cell A1(which will be an Number) and then look over 3
columns to the right and if it says lets say PAID then delete row and move to
cell A2 if it doesn't say PAID then move to next row and repeat the process...
any ideas??

--
Message posted via http://www.officekb.com

Macro
 

sub deleterows()
Dim lastrow as Long, i as Long
lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
if lcase(cells(i,"D").value) = "paid" then
rows(i).delete
end if
Next
End Sub

It is best to work from the bottom up or you could miss values.

I interpret 3 rows over as being column D, if it is another column, change
the letter "D" to reflect that column.

There are other ways to do this using the Find method, but this is direct
and easy to understand.

--
Regards,
Tom Ogilvy

"Newbee via OfficeKB.com" wrote:

Does anyone have some ideas on how to do this? I'm trying to write a macro
that will look at cell A1(which will be an Number) and then look over 3
columns to the right and if it says lets say PAID then delete row and move to
cell A2 if it doesn't say PAID then move to next row and repeat the process...
any ideas??

--
Message posted via http://www.officekb.com

Macro
 

The easiest way may be to use datafilterautofilter and delete all at once.
Record a macr
OR

for each c in range("a2:a200")
if ucase(c.offset(,3))="PAID" then c.entirerow.delete
next c

--
Don Guillett
SalesAid Software

"Newbee via OfficeKB.com" <u27679@uwe wrote in message
news:6eef63c0b143a@uwe...
Does anyone have some ideas on how to do this? I'm trying to write a macro
that will look at cell A1(which will be an Number) and then look over 3
columns to the right and if it says lets say PAID then delete row and move
to
cell A2 if it doesn't say PAID then move to next row and repeat the
process...
any ideas??

--
Message posted via http://www.officekb.com

Macro
 

for the code example Don provided, if you have two rows containing PAID, one
will be skipped. this is the reason I said loop backwards.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote:

The easiest way may be to use datafilterautofilter and delete all at once.
Record a macr
OR

for each c in range("a2:a200")
if ucase(c.offset(,3))="PAID" then c.entirerow.delete
next c

--
Don Guillett
SalesAid Software

"Newbee via OfficeKB.com" <u27679@uwe wrote in message
news:6eef63c0b143a@uwe...
Does anyone have some ideas on how to do this? I'm trying to write a macro
that will look at cell A1(which will be an Number) and then look over 3
columns to the right and if it says lets say PAID then delete row and move
to
cell A2 if it doesn't say PAID then move to next row and repeat the
process...
any ideas??

--
Message posted via http://www.officekb.com