# Macro for Deleting Cells Based on Criteria

#### youra6

##### Board Regular
I don't think this should be too hard, sure it would be a cinch for you guys.

There are two columns, Column A, and Column J.

 A J AC/NXX/099X AC/NXX/099X AC/NX000/XXX AC/NX000/XXX

<colgroup><col><col></colgroup><tbody>
</tbody>

If J2=A2, then delete the corresponding cell in A2.
If J3=A3, then delete the corresponding cell in A3.

And so on and so forth.

So the result after the macro would be:

 A J AC/NXX/099X AC/NX000/XXX

<colgroup><col><col></colgroup><tbody>
</tbody>

That's pretty much it!

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Code:
``````Sub DeleteALLTheCells()

For x = 1 To Cells(Rows.Count, "J").End(xlUp).Row
If Cells(x, 1).Value = Cells(x, 10).Value Then Cells(x, 1).Clear
Next x

End Sub``````

As always, I bet Rick will come up with a non-looping one with some fancy range jazz.

Code:
``````Sub DeleteALLTheCells()

For x = 1 To Cells(Rows.Count, "J").End(xlUp).Row
If Cells(x, 1).Value = Cells(x, 10).Value Then Cells(x, 1).Clear
Next x

End Sub``````

As always, I bet Rick will come up with a non-looping one with some fancy range jazz.

lol @ "DeleteAlltheCells". Thanks will give it a try.

Code:
``````Sub DeleteALLTheCells()

For x = 1 To Cells(Rows.Count, "J").End(xlUp).Row
If Cells(x, 1).Value = Cells(x, 10).Value Then Cells(x, 1).Clear
Next x

End Sub``````

As always, I bet Rick will come up with a non-looping one with some fancy range jazz.

Hmm, this macro code deletes the cells in A, but then also deletes everything in column J and --->

Hmm, this macro code deletes the cells in A, but then also deletes everything in column J and --->

That's....impossible. Is J to End formulas off of column A? Because it really is impossible that the code would do that. The only change to the sheet is:

Code:
``Cells(x, 1).Clear``

That clears cell A(x)

That's....impossible. Is J to End formulas off of column A? Because it really is impossible that the code would do that. The only change to the sheet is:

Code:
``Cells(x, 1).Clear``

That clears cell A(x)

You're right, at first glance it looked like everything was gone, but I just noticed that my formulas remained. I see nothing in the cells themselves.

I realize why now. Everything was linked together by formulas. ok, i know what to do now.

So your formulas are probably running off of column A...do you want those formulas to be pasted to values before A(x) is deleted? Or maybe you should move them to column J?

So your formulas are probably running off of column A...do you want those formulas to be pasted to values before A(x) is deleted? Or maybe you should move them to column J?

I thought about doing that, pasting to values... but this spreadsheet needs to be updated constantly, so the formulas need to remain.

I can create two versions of the Excel file. One version with the formulas, and one version with the values pasted. Unless you can think of a better way.

I'm just wondering what purpose deleting column A values serves? Is there another way to achieve the end result?

I'm just wondering what purpose deleting column A values serves? Is there another way to achieve the end result?

Long story short, I have to import this crappy customer spreadsheet into the CMDB and create relational links. It has to be in a certain format.

Replies
4
Views
228
Replies
1
Views
162
Replies
2
Views
309
Replies
5
Views
386
Replies
8
Views
410

Threads
1,203,269
Messages
6,054,476
Members
444,727
Latest member
Mayank Sharma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back