Macro for Deleting Cells Based on Criteria

[youra6]

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!

 

[NeonRedSharpie]

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.

 

[youra6]

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.

 

[youra6]

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 --->

 

[NeonRedSharpie]

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:

Cells(x, 1).Clear

That clears cell A(x)

 

[youra6]

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:

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.

 

[NeonRedSharpie]

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?

 

[youra6]

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.

 

[NeonRedSharpie]

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

 

[youra6]

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.