VBA code for deleting row from a table works only sometimes.

0zero0 · Jun 22nd 2017

I have the following code which deletes a row from a table ("EPAtable"). It deletes the row which contains the value selected in the "EPAlist" listbox which is on a userform.

Code

dim lrow as long
dim str as string


On Error Resume Next
lrow = Application.WorksheetFunction.Match(EPAlist.value, Range("EPATable").Columns(1), 0)
On error GoTo 0
If lrow > 0 Then
     Sheet12.Range("EPATable").Rows([lrow]).EntireRow.Delete
End If
End If


With EPAList
     str = .RowSource
     .RowSource = vbNullString
     .Rowsource = str
End With


End Sub

Display More

This code works only sometimes. Sometimes when I run the macro, nothing happens, I get an error or excel crashes.

I am just learning VBA, so perhaps there is a better way to accomplish what it is I am trying to accomplish?

**alansidman** · Jun 22nd 2017

Re: VBA code for deleting row from a table works only sometimes.

Upload a sample workbook so that we can test your code and make appropriate amendments.

**KjBox** · Jun 23rd 2017

Re: VBA code for deleting row from a table works only sometimes.

Try the attached example. Click on the button to show the UserForm then click on an item in the ListBox to delete the corresponding row in the table and update the EPAList.

Code in the UserForm is:

Code

Private Sub UserForm_Initialize()
    Dim x, i As Long
    
    x = [EPAtable[Column1]]  '// Change Column1 to suit the actual header of the first column of the "EPAtable" Table
    For i = 2 To UBound(x, 1)
        EPAList.AddItem x(i, 1)
    Next
        
End Sub


Private Sub EPAList_Click()
    Dim x, i As Long
    
    x = [EPAtable[Column1]]  '// Change Column1 to suit the actual header of the first column of the "EPAtable" Table
    i = Application.Match(EPAList, x, 0)
    Sheets("Sheet1").ListObjects("EPAtable").ListRows(i).Delete '// Change sheet name to suit
    EPAList.Clear
    x = [EPAtable[Column1]]  '// Change Column1 to suit the actual header of the first column of the "EPAtable" Table
    For i = 2 To UBound(x, 1)
        EPAList.AddItem x(i, 1)
    Next
    EPAList.ListIndex = -1
    
End Sub

Display More

Note that the ListBox is loaded with individual items rather than using the RowSource Property, better practice to do it that way. You will need to change the Sheet name to your actual sheet name for the sheet that has the "EPAtable" Table. Also, change Column1 (in 3 paces) to suit the actual header of the first column of the "EPAtable" Table.

VBA code for deleting row from a table works only sometimes.

Participate now!

Share