Consulting

Results 1 to 3 of 3

Thread: Solved: Alter code to clear contents not delete row.

  1. #1
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    246
    Location

    Solved: Alter code to clear contents not delete row.

    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
    [VBA]cell.entirerow.delete[/VBA]
    for
    [VBA]cells.entirerow.clearcontents[/VBA]
    But this is not clearing the entire row only the match in column A

    Any thoughts


    [VBA]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 [/VBA]

    Thanks for any help.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    That code is only looking through column A for a match. Changing this line[VBA]Set cell = sh.Cells.Find(Me.ComboBox1.Value) [/VBA]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.

  3. #3
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    246
    Location
    Thanks mike that has solved my problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •