Clear Contents of Adjacent Cell on Conditional Basis

[DoooWhat]

I want to clear the contents of certain cells if other data on the
same line meets my criteria. For instance, if cell V8=1, I want to
clear the contents (not delete) of cell M8.

I have some code that gets close, but doesn't quite get me what I
want. I have tried to tinker with it, but to no avail.

------------------------------------------------------------------

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(8).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "V").Value) Then
ElseIf .Cells(Lrow, "V").Value = "1"
Then .Rows(Lrow).Delete

'''''''''''''''''' .Rows(Lrow).Delete should be replaced
with something that
tells it to clear contents of the cell in column
M that corresponds
to a value of 1 in column V

End If
Next
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

 

[Ian]

What do you mean by clear but not delete. They are essentially the same.
Either the data is there, or it isn't.

One option might be to change the font colour in the cell to match the
background.

Assuming the font colour option is useful and that column V is the one you
are comparing to in each case, then you could use conditional formatting
instead of code.

 

[Gary''s Student]

Look at:

Sub marine()

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row

For i = nFirstRow To nLastRow
If Cells(i, "V").Value = 1 Then
Cells(i, "M").Clear
End If
Next
End Sub

 

[DoooWhat]

Ian:

The reason I say "clear contents" instead of "delete" is that deleting
a cell typically moves cells around (shift cells up, left, etc). I'm
speaking of the situation when you can either select a cell and hit
the DELETE button, or you can right click on a cell and click delete.
Right clicking will ask you how you want to shift the cells. Clicking
delete simply clears the contents.

My purpose is to get rid of the data for calculation purposes, not for
viewing, so conditional formatting will not help me (in this case).

Kevin

 

[DoooWhat]

Bingo! Thanks so much. Gary would be proud.

You guys rock.

Kevin

 

[Ian]

Replace .Rows(Lrow).Delete with .Cells(Lrow, "M").Value = ""

 

[Dave Peterson]

If you only wanted to clear the contents (not the formatting), then this:

Cells(i, "M").Clear
would be:
Cells(i, "M").ClearContents

 

[DoooWhat]

Thanks Ian. That method also worked. I appreciate the quick and
effective response from both of you.

Kevin

 

[DoooWhat]

Hmmm. I'll make a note of that, as it may help me sometime down the
road. Thanks for the tip.

Kevin