delete rows based on 2 conditions of that row
 

Hi there,
I have a spreadsheet that had data occupied from column A to T. How can I
create a macro that would look at just column E and G. Starting from row 2,
for each row, if cell in column E had the first 4 letters BSDT AND for each
cell in column E, if the first 2 letters are CA...then keep that row. If not,
then delete that entire row. Keep doing that until there is a blank line.
Now, cells in column E can be something like BSDTU33AITD and cells in column
G can be something like CA28464746

Thanks for your advise.

delete rows based on 2 conditions of that row
 

for rw = range("A1").End(xldown).Row to 2 step -1
if NOT ( left(cells(rw,"E").Value,4) = "BSDT" AND
left(cells(rw,"G").Value,2) = "CA") Then
rows(rw).delete
end if
next


you could also add an auto filter and then you'd just cut/paste the rows...



"Don Doan" wrote in message
...
Hi there,
I have a spreadsheet that had data occupied from column A to T. How can I
create a macro that would look at just column E and G. Starting from row
2,
for each row, if cell in column E had the first 4 letters BSDT AND for
each
cell in column E, if the first 2 letters are CA...then keep that row. If
not,
then delete that entire row. Keep doing that until there is a blank line.
Now, cells in column E can be something like BSDTU33AITD and cells in
column
G can be something like CA28464746

Thanks for your advise.

delete rows based on 2 conditions of that row
 

Thanks,
It works perfectly.

"Patrick Molloy" wrote:


for rw = range("A1").End(xldown).Row to 2 step -1
if NOT ( left(cells(rw,"E").Value,4) = "BSDT" AND
left(cells(rw,"G").Value,2) = "CA") Then
rows(rw).delete
end if
next


you could also add an auto filter and then you'd just cut/paste the rows...



"Don Doan" wrote in message
...
Hi there,
I have a spreadsheet that had data occupied from column A to T. How can I
create a macro that would look at just column E and G. Starting from row
2,
for each row, if cell in column E had the first 4 letters BSDT AND for
each
cell in column E, if the first 2 letters are CA...then keep that row. If
not,
then delete that entire row. Keep doing that until there is a blank line.
Now, cells in column E can be something like BSDTU33AITD and cells in
column
G can be something like CA28464746

Thanks for your advise.