Clear cell contents based on multiple criteria

JADownie

Active Member
Joined
Dec 11, 2007
Messages
363
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!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
seems like an easy enough task, try this:

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

Active Member
Joined
Dec 11, 2007
Messages
363
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

Board Regular
Joined
Jun 22, 2011
Messages
116
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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

Which adblocker are you using?

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
Top