I have written this to delete the row of cells within the selected range when
the value "Rock Breaking" is in a cell in the row.

It is however deleting the entire row.
How do I stop this?


Sub tmp3()

Dim rngOfData As Range
Dim Cell As Range

With Sheets("Daily Report")
Set rngOfData = .Range("DA20:DE29")
End With

For Each Cell In rngOfData
If Cell.Value = "Rock Breaking" Then
Cell.Row.Delete
End If
Next Cell

End Sub

It is doing what you have told it to do in the code. Where you have:

Cell.Row.Delete

If you only want to delete the contents of a portion of the row, then
for columns A - D:

Range(Cell.Offset(0, -3. Cell.Address).ClearContents

"Kirsty" wrote:

I have written this to delete the row of cells within the selected range when
the value "Rock Breaking" is in a cell in the row.

It is however deleting the entire row.
How do I stop this?


Sub tmp3()

Dim rngOfData As Range
Dim Cell As Range

With Sheets("Daily Report")
Set rngOfData = .Range("DA20:DE29")
End With

For Each Cell In rngOfData
If Cell.Value = "Rock Breaking" Then
Cell.Row.Delete
End If
Next Cell

End Sub

I have tried that now I am getting an error that states Expected: list
separator or ) before the Cell.Address

"JLGWhiz" wrote:

It is doing what you have told it to do in the code. Where you have:

Cell.Row.Delete

If you only want to delete the contents of a portion of the row, then
for columns A - D:

Range(Cell.Offset(0, -3. Cell.Address).ClearContents

"Kirsty" wrote:

I have written this to delete the row of cells within the selected range when
the value "Rock Breaking" is in a cell in the row.

It is however deleting the entire row.
How do I stop this?


Sub tmp3()

Dim rngOfData As Range
Dim Cell As Range

With Sheets("Daily Report")
Set rngOfData = .Range("DA20:DE29")
End With

For Each Cell In rngOfData
If Cell.Value = "Rock Breaking" Then
Cell.Row.Delete
End If
Next Cell

End Sub

Try this. Notice the dot placement

Sub deletepartofrowif()
With Sheets("Daily Report")
Set rng = .Range("DA20:DE29")
For Each c In rng
If c = "Rock Breaking" then
.Range(.Cells(c.Row, "da"),.Cells(c.Row, "de")).ClearContents
End If
Next c
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kirsty" wrote in message
...
I have written this to delete the row of cells within the selected range
when
the value "Rock Breaking" is in a cell in the row.

It is however deleting the entire row.
How do I stop this?


Sub tmp3()

Dim rngOfData As Range
Dim Cell As Range

With Sheets("Daily Report")
Set rngOfData = .Range("DA20:DE29")
End With

For Each Cell In rngOfData
If Cell.Value = "Rock Breaking" Then
Cell.Row.Delete
End If
Next Cell

End Sub

Sorry about that. Should have been:

Range(Cell.Offset(0, -3), Cell.Address).ClearContents

You would have to modify the Range parameters if you want cells different
than A thru D of that row.


"Kirsty" wrote:

I have tried that now I am getting an error that states Expected: list
separator or ) before the Cell.Address

"JLGWhiz" wrote:

It is doing what you have told it to do in the code. Where you have:

Cell.Row.Delete

If you only want to delete the contents of a portion of the row, then
for columns A - D:

Range(Cell.Offset(0, -3. Cell.Address).ClearContents

"Kirsty" wrote:

I have written this to delete the row of cells within the selected range when
the value "Rock Breaking" is in a cell in the row.

It is however deleting the entire row.
How do I stop this?


Sub tmp3()

Dim rngOfData As Range
Dim Cell As Range

With Sheets("Daily Report")
Set rngOfData = .Range("DA20:DE29")
End With

For Each Cell In rngOfData
If Cell.Value = "Rock Breaking" Then
Cell.Row.Delete
End If
Next Cell

End Sub