CODEWITHSUNDEEP
excelvba
Goston
Goston

Reputation: 13

Why does deleting listrows using variables not work?

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

Upvotes: 1

Views: 604

Answers (3)

Jonas_Hess
Jonas_Hess

Reputation: 2018

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

Upvotes: 0

OldUgly
OldUgly

Reputation: 2119

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.

enter image description here

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

enter image description here

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.

enter image description here

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

enter image description here

Upvotes: 1

Marco Vos
Marco Vos

Reputation: 2967

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

Upvotes: 1

Related Questions