Clear cell contents based on multiple criteria

[JADownie]

Is it possible to have a macro which would clear a cell value if 2 conditions are met in other columns.

Example: If M2 = blank AND if N2= blank, then clear the contents of cell O2. Ultimately I would like the macro to do something like this for every active cell in column O.

Is that something that could be possible using VBA, or is that too complex?

Thanks!

 

[jfish1288]

seems like an easy enough task, try this:

Sub ClearContents()
Dim LastRow As Integer
Dim Row As Integer

LastRow = Range("O" & Rows.Count).End(xlUp).Row

For Row = 2 To LastRow

If Cells(Row, "M") = "" And Cells(Row, "N") = "" Then
Cells(Row, "O").Clear
End If

Next

End Sub

 

[JADownie]

Thanks! That seems to almost work for me now! I pasted your code below into my workbook, and the code starts to run - i.e. values in column O do get cleared based on the blank conditions at M & N, but then I get a runtime error at this line and it will not finish...

If Cells(Row, "M") = "" And Cells(Row, "N") = "" Then

Thoughts why I am getting that error?

 

[jfish1288]

I am not sure why you might be getting a runtime there. The fact that you said that it does work, at least to start, means that it is looping through each row clearing the cell in O if need be. But at some row it is encountering a problem. I can not foresee what that problem might be.

See if you can figure out what row the loop is currently on when it encounters the error. You should be able to do this by attempting to run the code, clicking debug when you get the runtime error, and then placing the cursor on top of "Row". A light yellow box will pop up under the cursor and say "Row =" and some number. Then go to your spread sheet and see what is going on at that row.

I do not know what might be causing this problem, maybe someone else does. When I test the code it seems to work fine for me.