Show data in listbox and delete row in sheet based on listbox selection.

[kinetekerz]

Hi all!

I have the following code which MickG has helped me with on this forum.

Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As IntegerWith Sheets("Candidates")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
ReDim ray(1 To Rng.Count, 1 To 5) 'Extra column added (4 to 5)
    For Each Dn In Rng
        If Dn.Value = TextBox1.Value Then
            c = c + 1: Ac = 0
            For Each p In Array(-6, -5, -1, 0, 5) '(5) added, for column "N" which is 5 columns offset from "I"
                Ac = Ac + 1
                ray(c, Ac) = Dn.Offset(, p).Value
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 5 'Column added
    .ColumnWidths = "50,50,60,75,50" 'Column added
    .List = ray
End With

I am looking to adapt it to as well as being able to pull selected data from the sheet, i am looking at being able to select the line from the listbox, to delete the associated row in the sheet?

Im quite new to this and unsure how to change the code for this feature to be implemented?

If this cant work, is there a workaround?

 

[MickG]

You need to add a helper column in the listbox as per code below and add a CommandButton to the form that find the selected row in sheet1 and deletes it.

Private Sub ComboBox1_Change()
Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
'Extra column added (4 to 5)
ReDim ray(1 To Rng.Count, 1 To 6)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
      
      '(5) added, for column "N" which is 5 columns offset from "I"
            For Each p In Array(-6, -5, -1, 0, 5, "Ad")
                Ac = Ac + 1
                 If p = "Ad" Then
                   ray(c, Ac) = Dn.Row
                Else
                  ray(c, Ac) = Dn.Offset(, p).Value
                End If
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 6 'Column added
    .ColumnWidths = "50,50,50,50,50,50" 'Column added
    .List = ray
End With
End Sub


Private Sub CommandButton1_Click()
With ListBox1
    If .ListIndex > -1 Then
        Sheets("Sheet1").Rows(.List(.ListIndex, 5)).EntireRow.Delete
    End With
End With
End Sub

 

[MickG]

Regarding the formatting Try modifying the line as below,in red:-

For Each p In Array(-6, -5, -1, 0, 5, "Ad")
                Ac = Ac + 1
                 If p = "Ad" Then
                   ray(c, Ac) = Dn.Row
                Else
[COLOR=#ff0000]                  ray(c, Ac) = IIf(p = 0, Format(Dn.Offset(, p).Value, "00000000000"), Dn.Offset(, p).Value)[/COLOR]
                End If
            Next p

 

[kinetekerz]

Regarding the formatting Try modifying the line as below,in red:-

For Each p In Array(-6, -5, -1, 0, 5, "Ad")
                Ac = Ac + 1
                 If p = "Ad" Then
                   ray(c, Ac) = Dn.Row
                Else
[COLOR=#ff0000]                  ray(c, Ac) = IIf(p = 0, Format(Dn.Offset(, p).Value, "00000000000"), Dn.Offset(, p).Value)[/COLOR]
                End If
            Next p

Just tested at a different PC and thats working a charm, thanks!

In regards to the "Delete" command button, what code would need to be associated with that to delete the row that we discussed above?

Again, many thanks for all the help!

 

[kinetekerz]

I should note the change of PC was due to leaving work, not that it wasn't working on one and not the other!

 

[kinetekerz]

You need to add a helper column in the listbox as per code below and add a CommandButton to the form that find the selected row in sheet1 and deletes it.

Private Sub ComboBox1_Change()
Dim Rng As Range, Dn As Range, p As Variant, c As Long, Ac As Integer
With Sheets("Sheet1")
    Set Rng = .Range(.Range("I1"), .Range("I" & Rows.Count).End(xlUp))
End With
'Extra column added (4 to 5)
ReDim ray(1 To Rng.Count, 1 To 6)
    For Each Dn In Rng
        If Dn.Value = ComboBox1.Value Then
            c = c + 1: Ac = 0
      
      '(5) added, for column "N" which is 5 columns offset from "I"
            For Each p In Array(-6, -5, -1, 0, 5, "Ad")
                Ac = Ac + 1
                 If p = "Ad" Then
                   ray(c, Ac) = Dn.Row
                Else
                  ray(c, Ac) = Dn.Offset(, p).Value
                End If
            Next p
        End If
Next Dn
With ListBox1
    .Clear
    .ColumnCount = 6 'Column added
    .ColumnWidths = "50,50,50,50,50,50" 'Column added
    .List = ray
End With
End Sub


Private Sub CommandButton1_Click()
With ListBox1
    If .ListIndex > -1 Then
        Sheets("Sheet1").Rows(.List(.ListIndex, 5)).EntireRow.Delete
    End With
End With
End Sub

Hmm, i tried the code below with the "Delete" command button mentioned, but it deleted the top row! Not the row in question...

Dim I As Long

With ListBox1
  For I = .ListCount - 1 To 0 Step -1
    If .Selected(I) Then
       .RemoveItem I
       Sheets("Candidates").Rows(I + 2).EntireRow.Delete
    End If
  Next I
End With

 

[MickG]

Sorry, I forgot to send the "Delete" code.
Place this in Commandbutton on userform.

Private Sub CommandButton1_Click()
With ListBox1
    If .ListIndex > -1 Then
        Sheets("Sheet1").Rows(.List(.ListIndex, 5)).EntireRow.Delete
    End With
End With
End Sub