I can't delete row from sheet by selected row in listbox on userform except last row

[abdelfattah]

H ello
I try deleting from sheet by selected row in listbox except the last row based on selected sheet from combobox , but doesn't happens anything !
here is the code

Private Sub CommandButton2_Click()
With Me.ListBox1
        If .ListIndex = .ListCount - 1 Then
            MsgBox "you have not permission to do that", vbExclamation
            Exit Sub
        End If
    End With
  Dim i As Integer
  Application.ScreenUpdating = False
  Set ws = Sheets(ComboBox1.Value)
  If MsgBox("Are you sure you want to delete this data row?", vbYesNo + vbQuestion, "Delete Row?") = vbYes Then
   With ws
    For i = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
      If .Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        .Rows(i).Delete
      End If
    Next i
    End With
  End If
  Application.ScreenUpdating = True
End Sub

I hope somebody has idea .

 

[Akuini]

Hi Akuini,
I just want deleting row selection ,but your suggestion will delete all of rows contains duplicated date .

Could you post a sample data & explain what you're trying to do in more detail?

 

[abdelfattah]

CRT.xlsm
	A	B	C	D	E
1	DATE	ID	INPUT	OUTPUT	BALANCE
2	2022/11/21	IDD-CLOTT100		3,420.00	-3,420.00
3	2022/11/21	IDD-CLOTT101		3,350.00	-6,770.00
4	2022/11/21	IDD-CLOTT102	5,270.00		-1,500.00
5	2022/11/21	IDD-CLOTT103	1,500.00		0.00
6	2022/11/22	IDD-CLOTT104		840.00	-840.00
7	2022/11/22	IDD-CLOTT105		2,550.00	-3,390.00
8	2022/11/23	IDD-CLOTT106	3,390.00		0.00
9	2022/11/24	IDD-CLOTT107		980.00	-980.00
10	2022/11/25	IDD-CLOTT108		300.00	-1,280.00
11	2022/11/26	IDD-CLOTT109		300.00	-1,580.00
12	2022/11/27	IDD-CLOTT110	1,580.00		0.00
13	TOT		11,740.00	11,740.00	0.00
CMT

Cell Formulas
Range	Formula
C13:D13	C13	=SUM(C2:C12)
E13	E13	=C13-D13

before

after

CRT.xlsm
	A	B	C	D	E
1	DATE	ID	INPUT	OUTPUT	BALANCE
2	2022/11/21	IDD-CLOTT100		3,420.00	-3,420.00
3	2022/11/21	IDD-CLOTT101		3,350.00	-6,770.00
4	2022/11/21	IDD-CLOTT102	5,270.00		-1,500.00
5	2022/11/22	IDD-CLOTT104		840.00	-840.00
6	2022/11/22	IDD-CLOTT105		2,550.00	-3,390.00
7	2022/11/23	IDD-CLOTT106	3,390.00		0.00
8	2022/11/24	IDD-CLOTT107		980.00	-980.00
9	2022/11/25	IDD-CLOTT108		300.00	-1,280.00
10	2022/11/26	IDD-CLOTT109		300.00	-1,580.00
11	2022/11/27	IDD-CLOTT110	1,580.00		0.00
12	TOT		10,240.00	11,740.00	-1,500.00
CMT

Cell Formulas
Range	Formula
C12:D12	C12	=SUM(C2:C11)
E12	E12	=C12-D12

and should not delete the last row if the user try deleting as the original code does it.

 

[Akuini]

1. How did you populate the listbox? using .Rowsource or .List?
2. Do you want to delete the row only in the sheet or also in the listbox?

 

[abdelfattah]

1. How did you populate the listbox? using .Rowsource or .List?

by select sheet from com combobox1
I use this code

Private Sub UserForm_Initialize()
 
 Crit = ""
  Dim i As Long
  For i = 1 To Sheets.Count
   If Sheets(i).Name <> "MAIN" And Sheets(i).Name <> "DATA" Then
   ComboBox1.AddItem Sheets(i).Name
   End If
  Next
  
  If ComboBox1.ListIndex > -1 Then
    Set ws = Sheets(ComboBox1.Value)
    Call LBoxPop
    
  End If

also in the listbox?

yes for two both

 

[Akuini]

Call LBoxPop

Can you show us the code?

 

[Akuini]

I assume that data in col B is unique. So you can use that to identify the correct row to delete. Maybe like this:

Private Sub CommandButton2_Click()
With Me.ListBox1
        If .ListIndex = .ListCount - 1 Then
            MsgBox "you have not permission to do that", vbExclamation
            Exit Sub
        End If
    End With

  Application.ScreenUpdating = False
  Set ws = Sheets(ComboBox1.Value)
  If MsgBox("Are you sure you want to delete this data row?", vbYesNo + vbQuestion, "Delete Row?") = vbYes Then
        Set c = ws.Range("B:B").Find(What:=ListBox1.List(ListBox1.ListIndex, 1), LookIn:=xlValues, lookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            c.EntireRow.Delete
            ListBox1.RemoveItem ListBox1.ListIndex
        Else
            MsgBox "Item not found"
        End If
  End If
  Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

Can you show us the code?

sorry for delaying !

I assume that data in col B is unique. So you can use that to identify the correct row to delete. Maybe like this:

it's work excellently !
thank you so much .

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.