Deleting a cell if the cell below contains a specific text

[chenmoti]

I'm wondering if it's possible to delete a cell if the cell below it contains a specific text.

For example, In cell A1 the value is Mark, cell A2 is James and cell A3 is ABC. The program will now delete James because the cell below contains ABC.

How do you do it in VBA?

Thanks in advance!

 

[bebo021999]

Dim lr&, i&
lr = cells(rows.count,"A").end(xlup).row
For i = lr to 2 step -1
      If cells(i,1).value = "ABC" then  cell(i-1,1).clearcontents
Next

 

[chenmoti]

Dim lr&, i&
lr = cells(rows.count,"A").end(xlup).row
For i = lr to 2 step -1
      If cells(i,1).value = "ABC" then  cell(i-1,1).clearcontents
Next

It doesn't work

 

[Beginer1026]

It doesn't work

it will work after adding an "s" to the line below :

If cells(i,1).value = "ABC" then cells (i-1,1).clearcontents

 

[chenmoti]

it will work after adding an "s" to the line below :

If cells(i,1).value = "ABC" then cells (i-1,1).clearcontents

It doesn't work even though I changed it.

 

[bebo021999]

It works for me like this

BEFORE

AFTER

 

[Beginer1026]

It works for me like this

BEFORE
View attachment 75481

AFTER

View attachment 75482

The program cleared the contents of A2 "bbb" and A5 "gggg" because the cells below contain "ABC".

What results do you actually expect ?

 

[bebo021999]

What results do you actually expect ?

Opp, not me!
I am helper too, not OP.

 

[Beginer1026]

Opp, not me!
I am helper too, not OP.

Oh, sorry....I'm a newbie to this forum

 

[chenmoti]

Nvm, it worked now. Thank you! Last question, how do you shift up the cells to remove the blank cells?