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

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

As always, any help on this issue would be much appreciated. Thanks!

Kevin

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.
--
Ian
--
"DoooWhat" wrote in message
oups.com...
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

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

As always, any help on this issue would be much appreciated. Thanks!

Kevin

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


--
Gary''s Student
gsnu200709


"DoooWhat" wrote:

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

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

As always, any help on this issue would be much appreciated. Thanks!

Kevin

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

Bingo! Thanks so much. Gary would be proud.

You guys rock.

Kevin

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

--
Ian
--
"DoooWhat" wrote in message
ups.com...
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

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

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



DoooWhat wrote:

Bingo! Thanks so much. Gary would be proud.

You guys rock.

Kevin

--

Dave Peterson

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

Kevin

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

Kevin

On Mar 7, 9:32 am, Dave Peterson wrote:
If you only wanted to clear the contents (not the formatting), then this:

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