Deleting from tree view

Im useing the code below to delete items from both my treeview and my worksheet, problem i now have is that i am displaying only certain items from my spreadsheet, not every row

can anyone offer me a method (Or a start of a method) of being able to delete the correct row in the spreadsheet

Thanks Gibbo

Code:

---

Private Sub Image3_Click()
'Delete
'Find the correct row for the correct sheet
'Make Changes to the treeview parent before actual delete to allow correct calculations
If TreeView1.SelectedItem.Parent Is Nothing Then Exit Sub
'Is this a child with information
del = Trim(Left(TreeView1.SelectedItem.Parent.Text, Application.WorksheetFunction.Find(" ", TreeView1.SelectedItem.Parent.Text, 1)))
If MsgBox("Are you sure you want to delete " & TreeView1.SelectedItem.Text, vbYesNo + vbCritical, "Confirm Delete") = vbYes Then
Sheets(del).Rows(TreeView1.SelectedItem.Index - TreeView1.SelectedItem.FirstSibling.Index + 2).Delete
TreeView1.SelectedItem.Parent.Text = Trim(Left(TreeView1.SelectedItem.Parent.Text, Application.WorksheetFunction.Find(" ", TreeView1.SelectedItem.Parent.Text, 1))) & " (" & TreeView1.SelectedItem.Parent.Children - 1 & ")"
TreeView1.Nodes.Remove (TreeView1.SelectedItem.Index)
Label1.Caption = TreeView1.Nodes.Count - 3 & " Records Found For: " & Environ("Username")
Else
Exit Sub
End If
End Sub

---

Ok, i ve been considering using a search function to find my record in order to be able to delete it as my tree view items will be unique but it doesnt find the record, can anyone tell me why please

Code:

---

If TreeView1.SelectedItem.Parent Is Nothing Then Exit Sub 'Is this a child with information
del = Trim(Left(TreeView1.SelectedItem.Parent.Text, Application.WorksheetFunction.Find(" ", TreeView1.SelectedItem.Parent.Text, 1)))
If MsgBox("Are you sure you want to delete " & _
TreeView1.SelectedItem.Text, vbYesNo + vbCritical, "Confirm Delete") = vbYes Then
Dim SearchFor As Range
Set SearchFor = fnFind(TreeView1.SelectedItem.Text, del)
    If SearchFor Is Nothing Then
        MsgBox ("Sorry Not Found")
    Else
        MsgBox TreeView1.SelectedItem.Text
        'SearchFor.Value
    End If
    'Sheets(del).Rows(TreeView1.SelectedItem.Index - TreeView1.SelectedItem.FirstSibling.Index + 2).Delete
    TreeView1.SelectedItem.Parent.Text = Trim(Left(TreeView1.SelectedItem.Parent.Text, _
    Application.WorksheetFunction.Find _
    (" ", TreeView1.SelectedItem.Parent.Text, 1))) & " (" & TreeView1.SelectedItem.Parent.Children - 1 & ")"
    TreeView1.Nodes.Remove (TreeView1.SelectedItem.Index)
    Label1.Caption = TreeView1.Nodes.Count - 3 & " Records Found For: " & Environ("Username")
Else
Exit Sub
End If
End Sub
 
 Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
    On Error Resume Next
    Set fnFind = sh.Cells.Find(What:=strFind, _
    After:="A2", _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
End Function

---

Thanks

Gibbo

Tried a different approach that worked for as below

Thanks for looking

Gibbo

Code:

---

Dim i As Integer
If TreeView1.SelectedItem.Parent Is Nothing Then Exit Sub 'Is this a child with information
del = Trim(Left(TreeView1.SelectedItem.Parent.Text, Application.WorksheetFunction.Find(" ", TreeView1.SelectedItem.Parent.Text, 1)))
If MsgBox("Are you sure you want to delete " & TreeView1.SelectedItem.Text, vbYesNo + vbCritical, "Confirm Delete") = vbYes Then
i = Sheets(del).Range("A63556").End(xlUp).Row
For ii = 2 To i
    If Trim(TreeView1.SelectedItem.Text) = Trim(Sheets(del).Cells(ii, 1)) Then
        MsgBox TreeView1.SelectedItem.Text
        Sheets(del).Rows(ii).Delete
    End If
Next ii
    'Sheets(del).Rows(TreeView1.SelectedItem.Index - TreeView1.SelectedItem.FirstSibling.Index + 2).Delete
    TreeView1.SelectedItem.Parent.Text = Trim(Left(TreeView1.SelectedItem.Parent.Text, Application.WorksheetFunction.Find(" ", TreeView1.SelectedItem.Parent.Text, 1))) & " (" & TreeView1.SelectedItem.Parent.Children - 1 & ")"
    TreeView1.Nodes.Remove (TreeView1.SelectedItem.Index)
    Label1.Caption = TreeView1.Nodes.Count - 3 & " Records Found For: " & Environ("Username")
Else
Exit Sub
End If

---