Clearing a Row, when selecting an item on a TextBox, from the text box, and the source worksheet

Question

I have a problem while trying to clear an entire row of data from my listbox with a button, the idea is that I have a text box which source is sheet "x", and I want to be able to clear the text from this sheet, when I select an item from the listbox when I press a button.

this is what I have been trying:

Dim ws4 As Worksheet
Set ws4 = Worksheets("ALCANCES")

With ws4
Dim r1 As Range
Set r1 = ws4.Range("A1:A200")
For Each c1 In r1
    If c1.Value = listAlc.Value Then
    c1.Font.Bold = True
      With c1
      c1.EntireRow.Clear
       End With
    End If
Next c1
End With

This code works fine if instead of clear I enter Delete entire row, but I don't want to delete the row, I just want to clear the information on it, and this code sometimes does this, but sometimes it clears the row I selected and everything above it.

Thanks in advance.

Answer 1

in case anyone needs helps with this, I finally solved this.

listbox properties: name (lisAlcan) rowsource ALCANCES!A1:C300 boundcolum 3 columcount 2 controlsource ALCANCES!D1

and then I made two buttons one to add information and another to erase that information from the list and from the sheet:

Private Sub butAlgr_Click()

If txtAlcan.Value = "" Then
MsgBox "Ingrese el Alcance", vbOKOnly

Exit Sub
End If







 Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("ALCANCES")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row


    ws.Range("A" & LastRow).Value = "*" 'Adds the TextBox3 into Col A & Last Blank Row
    ws.Range("B" & LastRow).Value = txtAlcan.Text 'Adds the ComboBox1 into Col B & Last Blank Row

ws.Range("C" & LastRow).Value = LastRow





'clear the data
Me.txtAlcan.Value = ""
Me.txtAlcan.SetFocus

MsgBox "Datos registrados", vbOKOnly


End Sub

and the erase button:

Private Sub ButAlbo_Click()

Dim ws4 As Worksheet
Set ws4 = Worksheets("ALCANCES")
Dim fnd As Variant
Dim rplc As Variant


iRow = ws4.Range("d1").Value
ws4.Cells(iRow, 1).Value = Clear
ws4.Cells(iRow, 2).Value = Clear
ws4.Cells(iRow, 3).Value = Clear

End Sub

Answer 2

EDIT: finally found a solution, now I just need it to aply to the entire row where the word is hehe... but for now I can finally clear the data I select on a text box, when I use the 2 properties of the text box: BoundColum, and ControlSource, and this code:

Private Sub ButAlbo_Click()

Dim ws4 As Worksheet
Set ws4 = Worksheets("ALCANCES")
Dim fnd As Variant
Dim rplc As Variant


iRow = ws4.Range("d1").Value

fnd = iRow

rplc = Clear



  ws4.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False






End Sub