Why does deleting listrows using variables not work?

Question

I was wondering why my code doesn't work when I use a variable to delete a given row from a table when it works fine if I put a number in the brackets. The code doesn't do anything extraordinary. It goes through a cycle and finds the row I want to delete and then returns its number.

And there is the line I have the problem with: Inventory.ListRows(Talalat).Delete 'where Inventory is a Listobject and Talalat is Long

I definitely get the correct number from the code for the row I want to delete, and it runs fine without error messages but it just doesn't do what it has to. But when I insert the number manually into the code it works just fine... Any thoughts?

PS: the entire code is below if you'd want to look into it.

Private Sub CommandButton17_Click()
Dim CounterA As Long
Dim Talalat As Long
Dim ANsWR As Integer
Set invent = Sheets("CORE").ListObjects("Inventory")
If Not ComboBox4.Value = "" And Not ComboBox4.Value = "<Új tárgy>" Then
    Talalat = 0
        For CounterA = 1 To invent.Range.Rows.Count
            If ComboBox4.Value = invent.Range.Cells(CounterA, 1) Then Talalat = CounterA
        Next
        If Talalat > 0 Then
            ANsWR = MsgBox("Biztosan törli a(z) " & invent.Range.Cells(Talalat, 1) & Chr(13) & "nevű tárgyat az adatbázisból?", vbYesNo, "Tuti?")
            If ANsWR = vbNo Then
                Exit Sub
            Else
                Sheets("CORE").Unprotect
                invent.ListRows(Talalat).Delete 'Where the glitch is
            End If
        End If
End If
End Sub

Marco Vos · Accepted Answer · 2016-04-23 04:54:19Z

1

Are you absolutely sure you are getting the correct number for the row you want to delete? Try adding "-1" after:

If ComboBox4.Value = invent.Range.Cells(CounterA, 1) Then Talalat = CounterA

answered Apr 23, 2016 at 4:54

Marco Vos

2,9771 gold badge11 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Goston Over a year ago

I should sit the f*** down and have a rest when I'm tired. That was the problem. Thank you so much! I checked the number of listrows before and after the process and found that it's decreasing, so there was definitely a row deleted. Thanks again!

OldUgly · Accepted Answer · 2016-04-23 05:06:38Z

Do you have a header in your table? Assuming the answer is "Yes", below illustrates why you should use .DataBodyRange instead of .Range.

Here is some test code, similar to yours ...

Sub myTest()
Dim iLoop As Long
Dim theRow As Long
Dim userAns As Integer
Dim myList As ListObject
    Set myList = Sheets("Sheet5").ListObjects("Table1")
    theRow = 0
    For iLoop = 1 To myList.Range.Rows.Count
        If IsDate(myList.Range.Cells(iLoop, 1)) And myList.Range.Cells(iLoop, 1) > Now() Then
            theRow = iLoop
            Exit For
        End If
    Next iLoop

    If theRow > 0 Then
        userAns = MsgBox("Found " & myList.Range.Cells(theRow, 1) & " on Row " & theRow & ". Should I delete?", vbYesNo)
        If userAns = vbNo Then
            Exit Sub
        Else
            myList.ListRows(theRow).Delete
        End If
    End If
End Sub

Running this code against the below Table, with a header appears to provide the correct number. The first date in Column A that is greater than NOW is in Row 18.

However, when saying "Yes" to the dialog, we see it actually deletes Row 19.

The below code is the same, but all instances of myList.Range have been changed to myList.DataBodyRange. Also, IsDate(myList.DataBodyRange.Cells(iLoop, 1)) is removed, because the loop will not check the header anymore ...

Sub myTest()
Dim iLoop As Long
Dim theRow As Long
Dim userAns As Integer
Dim myList As ListObject
    Set myList = Sheets("Sheet5").ListObjects("Table1")
    theRow = 0
    For iLoop = 1 To myList.DataBodyRange.Rows.Count
        If IsDate(myList.DataBodyRange.Cells(iLoop, 1)) And myList.DataBodyRange.Cells(iLoop, 1) > Now() Then
            theRow = iLoop
            Exit For
        End If
    Next iLoop

    If theRow > 0 Then
        userAns = MsgBox("Found " & myList.DataBodyRange.Cells(theRow, 1) & " on Row " & theRow & ". Should I delete?", vbYesNo)
        If userAns = vbNo Then
            Exit Sub
        Else
            myList.ListRows(theRow).Delete
        End If
    End If
End Sub

When it is used against the same table, it says that the first data after NOW is on Row 17. This is the 17th row of the data, excluding the header.

When you click Yes, then the correct row is deleted ...

Thank you for the detailed information! It makes it a lot easier to understand what went wrong!

Jonas_Hess · Accepted Answer · 2017-07-19 11:44:46Z

0

We have encouraged problems deleting ListRows, when there was an AutoFilter defined on the ListObject. We were able to solve this by removing the filters before deleting rows:

myList.AutoFilter.ShowAllData
myList.ListRows(theRow).Delete

answered Jul 19, 2017 at 11:44

Jonas_Hess

2,0782 gold badges26 silver badges36 bronze badges

Collectives™ on Stack Overflow

Why does deleting listrows using variables not work?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related