VBA Excel delete rows with specific value

Question

I need to delete specific rows in a table with a order number. When I put this code in Excel it delete the entire table. What I want is to delete only specific rows. The tbxOrder is a text box. I want that when the text box equals x command number the code will select all the rows with the same number in the "Pagination" table (column 20) and then delete them. Thank you :)

Public Sub DeleteOrderRows()

Dim strNoOrder As String
Dim strNoFunction As String
Dim noCommande As Integer

Dim tblPagination As ListObject
Set tblPagination = Worksheets("Pagination").ListObjects.Item("tblPagination")

For Each srcrow In tblPagination.ListRows

strNoOrder = srcrow.Range.Cells(1, 20)
noOrder = tbxOrder.Value

If strNoOrder = noOrder Then

EntireRow.Delete

End If

Next

End Sub

Is that all of your code? You're missing a Next srcrow and an End If... Also, when you do srcrow.Range.Cells(1,20) (if that .Range.Cells() works), you're referring to the relative Cells(1,20) within srcRow. Is that what you mean? Are you not getting any error?? — BruceWayne
– BruceWayne, Commented Aug 16, 2017 at 20:07
Yes Excel VBA Run-time error '424': Object Required at EntireRow.Delete. — Nadine Ruttimann
– Nadine Ruttimann, Commented Aug 16, 2017 at 20:13

user4039065 · Accepted Answer · 2017-08-16 22:09:01Z

Try it without the ListRows; use DataBodyRange instead. Work from the bottom to the top or you risk skipping rows when you delete.

Option Explicit

Public Sub DeleteOrderRows()

    Dim strNoOrder As String
    Dim strNoFunction As String, noOrder As String
    Dim noCommande As Integer
    Dim i As Long, tbxOrder As Range
    Dim tblPagination As ListObject

    Set tblPagination = Worksheets("Pagination").ListObjects.Item("tblPagination")

    'here I had to set tbvOrder and assign noOrder
    Set tbxOrder = Worksheets("Pagination").Cells(1, "A")
    noOrder = tbxOrder.Value

    With tblPagination.DataBodyRange.Columns(20).Cells
        For i = .Count To 1 Step -1
            Debug.Print .Cells(i).Address(0, 0)
            strNoOrder = .Cells(i).Value2

            If strNoOrder = noOrder Then
                .Cells(i).EntireRow.Delete
            End If

        Next i
    End With

End Sub

Nadine Ruttimann · Accepted Answer · 2017-08-31 15:30:24Z

0

An other option :

Public Sub DeleteOrderRows()
Dim rngToDelete As Range
Set rngToDelete = Nothing

Dim tblPagination As ListObject
Set tblPagination = Worksheets("Pagination").ListObjects.Item("tblPagination")

Dim strNopage As String
Dim strNoOrder As String

For Each currentRow In tblPagination.ListRows
    strNoOrder = currentRow.Range.Cells(1, 5).Value
    strNopage = tbxPage.Value
    If strNoCommande = strNopage Then
        If rngToDelete Is Nothing Then
            Set rngToDelete = currentRow.Range
        Else
            Set rngToDelete = Union(rngToDelete, currentRow.Range)
        End If
    End If
Next

If Not rngToDelete Is Nothing Then
    rngToDelete.Delete Shift:=xlUp
End If

End Sub

answered Aug 31, 2017 at 15:30

Nadine Ruttimann

611 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

VBA Excel delete rows with specific value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related