PDA

View Full Version : Solved: Alter code to clear contents not delete row.



Barryj
01-16-2009, 08:21 PM
I need this code to delete contents in the row when a match, not delete the row as some of the rows are linked to other sheets.

I tried changing this line of code
cell.entirerow.delete
for
cells.entirerow.clearcontents
But this is not clearing the entire row only the match in column A

Any thoughts


Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim cell As Range

For Each sh In Worksheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"))

Set cell = Nothing
Set cell = sh.Columns(1).Find(Me.ComboBox1.Value)
If Not cell Is Nothing Then

cell.EntireRow.Delete
End If
Next sh
End Sub

Thanks for any help.

mikerickson
01-16-2009, 09:46 PM
That code is only looking through column A for a match. Changing this lineSet cell = sh.Cells.Find(Me.ComboBox1.Value) will make the routine search the whole sheet.

Either routine deletes/clears only the first occurence of the key term. All others are left in place.

Barryj
01-17-2009, 12:56 PM
Thanks mike that has solved my problem.