Office中国论坛/Access中国论坛

标题: 请各位高人帮帮忙！ [打印本页]

作者: ytzsky 时间: 2007-5-16 06:16
标题: 请各位高人帮帮忙！
通过点击＜停留＞表中的发出按钮来实现，＜停留＞表中与＜发出＞表中重复的数据所在行自动删掉，目前只能删掉数据，而不能删掉整行．请高手帮我解决．

Sub DelDups_TwoLists()

Dim iListCount As Integer

Dim iCtr As Integer

' Turn off screen updating to speed up macro.

Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).

iListCount = Sheets("停留").Range("A1:A100").Rows.Count

' Loop through the "master" list.

For Each x In Sheets("发出").Range("A1:A10")

' Loop through all records in the second list.

For iCtr = 1 To iListCount

   ' Do comparison of next record.

   ' To specify a different column, change 1 to the column number.

   If x.Value = Sheets("停留").Cells(iCtr, 1).Value Then

      ' If match is true then delete row.

      Sheets("停留").Cells(iCtr, 1).Delete xlShiftUp

      ' Increment counter to account for deleted row.

      iCtr = iCtr + 1

   End If

Next iCtr

Next

Application.ScreenUpdating = True

MsgBox "发出成功"

End Sub

Private Sub CommandButton1_Click()

Dim iListCount As Integer

Dim iCtr As Integer

' Turn off screen updating to speed up macro.

Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).

iListCount = Sheets("停留").Range("A1:A100").Rows.Count

' Loop through the "master" list.

For Each x In Sheets("发出").Range("A1:A10")

' Loop through all records in the second list.

For iCtr = 1 To iListCount

   ' Do comparison of next record.

   ' To specify a different column, change 1 to the column number.

   If x.Value = Sheets("停留").Cells(iCtr, 1).Value Then

      ' If matc

作者: 方漠 时间: 2007-5-16 17:44
Private Sub CommandButton1_Click()

Dim iListCount As Integer

Dim iCtr As Integer

' Turn off screen updating to speed up macro.

Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).

iListCount = Sheets("停留").Range("A1:A100").Rows.Count

' Loop through the "master" list.

For Each x In Sheets("发出").Range("A1:A10")

' Loop through all records in the second list.

For iCtr = 1 To iListCount

   ' Do comparison of next record.

   ' To specify a different column, change 1 to the column number.

   If x.Value = Sheets("停留").Cells(iCtr, 1).Value Then

      ' If match is true then delete row.

Rows(Sheets("停留").Cells(iCtr, 1).Row).Delete

  ' Sheets("停留").Cells(iCtr, 1).Delete xlShiftUp

      ' Increment counter to account for deleted row.

      iCtr = iCtr + 1

   End If

Next iCtr

Next

Application.ScreenUpdating = True

MsgBox "发出成功"

End Sub

作者: ytzsky 时间: 2007-5-16 20:05
上述问题已经解决了,非常感谢!!!

能否再请教一个问题?

<发出>表中不与<停留>表里的重复数据能不能显示出来?(如变颜色之类的)

谢谢您的鼓励!!!

NEVER GIVE UP!!!

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)