VBA代码仅在调试模式下运行时删除行

Question

我在非调试模式下运行代码时删除行时遇到问题。我把星星放在线旁边，这给了我一个问题。在调试模式下工作，但不能正常运行代码。有什么帮助吗？我尝试使用 doevent，但在 for 循环的开头，但这不起作用。

Public Sub ItemUpdate(ByVal startRow As Integer, ByVal endRow As Integer, ByVal itemCol As String, ByVal statusCol As String, ByVal manuPNCol As String)
Dim orgSheet As Worksheet
Dim commonSheet As Worksheet
Dim partDesCol As String
Dim partDes As String
Dim vendorColNumber As Integer
Dim vendorColLetter As String
Dim manuPN As String
Dim counter As Integer
Dim replaceRnge As Range
Set orgSheet = ThisWorkbook.ActiveSheet
partDesCol = FindPartDesCol()
Set commonSheet = ThisWorkbook.Worksheets("Common Equipment")
For counter = startRow To endRow
'Get part description value
partDes = Range(partDesCol & counter).Value
'Delete row of empty cells if there is any
If partDes = "" Then
'deleteing empty row
orgSheet.Rows(counter).Delete    '************************** Only works in                     
 debug mode.
endRow = endRow - 1
If counter < endRow Then
    counter = counter - 1
Else
    Exit For
End If
Else
manuPN = Range(manuPNCol & counter).Value
'Search for user part in common sheet
Set rangeFind = commonSheet.Range("1:200").Find(partDes, lookat:=xlWhole)
If rangeFind Is Nothing Or partDes = "" Then
Debug.Print "Part " & partDes & " not found in Common Equipment"
'MsgBox "Part " & partDes & " not found in Common Equipment"
'Now check if manuPN is in common equipment
Set rangeFind = commonSheet.Range("1:200").Find(manuPN, lookat:=xlWhole)
    If rangeFind Is Nothing Or partDes = "" Then
    Debug.Print "PartNumber " & manuPN & " not found in Common Equipment"
    'Now check if vendor value of item is empty
    'Get vendor col
    vendorCol = FindSearchCol()
    If orgSheet.Range(vendorCol & counter).Value = "" Then
    'Copy and paste manufact. data to vendor
    'converting from letter column to number and visa versa
    vendorColNumber = Range(vendorCol & 1).Column
    ManuColTemp = vendorColNumber - 2
    ManuPNColTemp = vendorColNumber - 1
    VendorPNColTemp = vendorColNumber + 1
    ManuCol = Split(Cells(1, ManuColTemp).Address(True, False), "$")(0)
    manuPNCol = Split(Cells(1, ManuPNColTemp).Address(True, False), "$")(0)
    VendorPNCol = Split(Cells(1, VendorPNColTemp).Address(True, False), "$")    
(0)
    orgSheet.Range(ManuCol & counter & ":" & manuPNCol & counter).Copy     Range(vendorCol & counter & ":" & VendorPNCol & counter)
End If
Else
'Copy new data from common equipment and paste in place of old data
'Get value of status
If statusCol <> "error" Then
    orderStatus = orgSheet.Range(statusCol & counter).Value
End If
commonSheet.Rows(rangeFind.Row).EntireRow.Copy
orgSheet.Range(itemCol & counter).PasteSpecial xlPasteValues
If statusCol <> "error" Then
    orgSheet.Range(statusCol & counter).Value = orderStatus
End If
End If
Else
'Copy new data from common equipment and paste in place of old data
'Get value of status
If statusCol <> "error" Then
    orderStatus = orgSheet.Range(statusCol & counter).Value
End If
commonSheet.Rows(rangeFind.Row).EntireRow.Copy
orgSheet.Range(itemCol & counter).PasteSpecial xlPasteValues
If statusCol <> "error" Then
    orgSheet.Range(statusCol & counter).Value = orderStatus
End If
End If
End If
Next counter
'call renumber item numbers
Call NumberItems(0, 0, 0, False)
End Sub

Answer 1

最有可能的是，您需要在范围内向后退一步。 当您向前迈进时，就像您所做的那样，只要您删除一行，计数器就会跳过一行：

For counter = startRow To endRow

更改为

For counter = endRow To startRow Step -1

此外，还应将endRow和startRow声明为数据类型Long。 Integer范围不会涵盖 Excel 工作表中的所有行;而且据说 VBA 在进行数学运算时可以将整数转换为长整型。