VBA Excel删除具有特定值的行

Question

我需要删除带有订单号的表中的特定行。当我将此代码放入Excel中时，它会删除整个表。我想要的是只删除特定的行。tbxOrder 是一个文本框。我希望当文本框等于x命令编号时，代码将选择"分页"表(第20列(中具有相同编号的所有行，然后删除它们。谢谢:)

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

Answer 1

尝试不使用 ListRows;请改用 DataBodyRange。从下到上工作，否则删除时可能会跳过行。

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

Answer 2

另一种选择：

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