Delete row of cells in range based on condition
 

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

Delete row of cells in range based on condition
 

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

Delete row of cells in range based on condition
 

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

Delete row of cells in range based on condition
 

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

Delete row of cells in range based on condition
 

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