我已经在VBA脚本上工作了一段时间了，现在它通过列的值并删除仅出现一次值的所有行(几乎是删除重复项的逆)。
列标头使解释更容易
"VTR"列中的数字出现了不止一次。大多数只出现一次。我希望宏删除所有行，其中"VTR"列中的数字只出现一次。(如果这些数字中的一个出现了不止一次，区别在于"AFTARTKRZ"列，其中的值可以是(GAPNK或GAPN2)或RSLNV或RSVNK。(GAPNK和GAPN2是一回事)
。当与AFTARTKRZ，
同时出现时，可以出现一次。(GAPNK or GAPN2)
<<p> - 或两次/strong>either (GAPNKorGAPN2)， RSLNVor (GAPNKorGAPN2)， RSVNK
三次或(GAPNK或GAPN2)、RSLNV、RSVNK。
我想删除那些只出现一次的(GAPNKorGAPN2)
此外，我想将重复项的'AFTARTKRZ'值添加到最后的2个额外列中。例如，当(GAPNK或GAPN2)出现两次或三次时，我想在最后2列中输入"AFTARTKRZ"列值。
像这样的东西应该是最终结果
VTR|AFTARTKRZ | Add1 | Add2
11 |GAPNK |RSLNV | RSVNK| - VTR appeared thrice
12 |GAPN2 |RSLNV | | - Appeared twice as (GAPNKorGAPN2), RSLNV
13 |GAPNK |RSVNK | | - Appeared twice as (GAPNKorGAPN2), RSVNK
14 |GAPN2 | |
15 |GAPNK | |
16 |GAPN2 | |
相关部分从'~~~~ Work on A
开始Sub Test()
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim RowsToTestC As Range, Delrange As Range
Dim i As Long, Lrow As Long, Lop As Long
Set ws1 = ThisWorkbook.Worksheets(1)
ThisWorkbook.ActiveSheet.Name = "A"
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.ActiveSheet.Name = "B"
Set ws2 = ThisWorkbook.Worksheets(2)
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.ActiveSheet.Name = "C"
Set ws2 = ThisWorkbook.Worksheets(3)
'~~~~ Work on C
Worksheets("C").Activate
With ActiveSheet
ActiveSheet.Range("A:AQ").RemoveDuplicates Columns:=6, Header:=xlNo
End With
Worksheets("C").Activate
Application.ScreenUpdating = False
'~~> Delete all but RSV
For Lrow = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("D" & Lrow).Value = "GAPNK" Or Range("D" & Lrow) = "GAPN2" Then
Rows(Lrow).EntireRow.Delete
End If
Next Lrow
'~~~~ Work on B
Worksheets("B").Activate
With ActiveSheet
ActiveSheet.Range("A:AQ").RemoveDuplicates Columns:=6, Header:=xlNo
End With
Worksheets("B").Activate
Application.ScreenUpdating = False
'~~> Delete all but GAP
For Lrow = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("D" & Lrow).Value = "RSVNK" Or Range("D" & Lrow) = "RSLNV" Then
Rows(Lrow).EntireRow.Delete
End If
Next Lrow
'~~~~ Work on A
Worksheets("A").Activate
Range("AR1").Select
ActiveCell.FormulaR1C1 = "RSVNK"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "RSLNV"
With ws1
'~~> Get the last row which has data in Col A
Lop = .Range("A" & .Rows.Count).End(xlUp).Row
'~~> Loop through the rows
For i = 2 To Lop
'~~> For for multiple occurances
If .Cells(i, 6).Value <> "" And .Cells(i, 4).Value <> "" Then
If Application.WorksheetFunction.CountIf(.Columns(6), .Cells(i, 6)) = 1 And _
Application.WorksheetFunction.CountIf(.Columns(4), .Cells(i, 4)) = 1 Then
'~~> Store thee row in a temp range
If Delrange Is Nothing Then
Set Delrange = .Rows(i)
Else
Set Delrange = Union(Delrange, .Rows(i))
End If
End If
End If
Next
End With
End Sub
您的代码逻辑无效。
条件If Application.WorksheetFunction.CountIf(.Columns(4), .Cells(i, 4)) = 1将始终是False，因为这是包含AFTARTKRZ键的列。我不知道你有多少行数据，但即使在你给我们的10行样本中，结果也总是大于1。
我确实认为你把事情弄得太复杂了。您不是在尝试填充两个列表:一个是gap，另一个是RSVs吗?然后您想要创建第三个列表，其中GAP条目具有相应的RSV条目?
这可以在几个简短的例程中完成。你可以省去所有的表单复制和行删除，只需将三个列表直接写入您的表单。
下面的代码向您展示了如何做到这一点。我已经创建了4个表，所以你可能需要添加另一个到你的工作簿:Sheet1是你的摘要列表(A)， Sheet2是你的GAP列表(B)， Sheet3是你的RSV列表(C)， Sheet4保存原始数据。
希望这段代码可以让你开始:
Option Explicit
Public Sub RunMe()
Const AFTARTKRZ_COL As Long = 4
Const VTR_COL As Long = 6
Dim data As Variant
Dim GAPs As Collection
Dim RSVs As Collection
Dim multis As Collection
Dim vtrKey As String
Dim multi(0 To 1) As Long
Dim i As Long, r As Long, c As Long
Dim v As Variant
Dim countRSV As Long
Dim output() As Variant
'Name your sheets.
'If you have fewer than 3 sheets or
'sheets already names A, B, C then this
'will throw an error.
Sheet1.Name = "A"
Sheet2.Name = "B"
Sheet3.Name = "C"
'Initialise the 3 collections
Set GAPs = New Collection
Set RSVs = New Collection
Set multis = New Collection
'Read the data - I've put my dummy data on Sheet4
data = Sheet4.UsedRange.Value2
'Iterate rows and place row in relevant collection
For r = 1 To UBound(data, 1)
vtrKey = CStr(data(r, VTR_COL))
On Error Resume Next 'removes duplicate entries
Select Case data(r, AFTARTKRZ_COL)
Case Is = "GAPNK", "GAPN2": GAPs.Add r, vtrKey
Case Is = "RSLNV": RSVs.Add r, vtrKey & "|RSLNV"
Case Is = "RSVNK": RSVs.Add r, vtrKey & "|RSVNK"
End Select
On Error GoTo 0
Next
'Check if each GAP also has RSVs
For Each v In GAPs
vtrKey = CStr(data(v, VTR_COL))
countRSV = 0
If Exists(RSVs, vtrKey & "|RSLNV") Then countRSV = countRSV + 1
If Exists(RSVs, vtrKey & "|RSVNK") Then countRSV = countRSV + 2
If countRSV > 0 Then
multi(0) = CLng(v)
multi(1) = countRSV
multis.Add multi, vtrKey
End If
Next
'Write your outputs
'Sheet C
ReDim output(1 To RSVs.Count + 1, 1 To UBound(data, 2))
For c = 1 To UBound(data, 2)
output(1, c) = data(1, c)
Next
i = 2
For Each v In RSVs
For c = 1 To UBound(data, 2)
output(i, c) = data(v, c)
Next
i = i + 1
Next
With Sheet3
.Cells.Clear
.Range("A1").Resize(UBound(output, 1), UBound(output, 2)).Value = output
.Columns.AutoFit
End With
'Sheet B
ReDim output(1 To GAPs.Count + 1, 1 To UBound(data, 2))
For c = 1 To UBound(data, 2)
output(1, c) = data(1, c)
Next
i = 2
For Each v In GAPs
For c = 1 To UBound(data, 2)
output(i, c) = data(v, c)
Next
i = i + 1
Next
With Sheet2
.Cells.Clear
.Range("A1").Resize(UBound(output, 1), UBound(output, 2)).Value = output
.Columns.AutoFit
End With
'Sheet A
ReDim output(1 To multis.Count + 1, 1 To 5)
output(1, 1) = "VTR"
output(1, 2) = "AFTARTKRZ"
output(1, 3) = "Add1"
output(1, 4) = "Add2"
i = 2
For Each v In multis
r = v(0)
output(i, 1) = data(r, VTR_COL)
output(i, 2) = data(r, AFTARTKRZ_COL)
output(i, 2) = data(r, AFTARTKRZ_COL)
Select Case v(1)
Case 1
output(i, 3) = "RSLNV"
output(i, 5) = "Appeared twice as (GAPNK or GAPN2), RSLNV"
Case 2
output(i, 3) = "RSVNK"
output(i, 5) = "Appeared twice as (GAPNK or GAPN2), RSVNK"
Case 3
output(i, 3) = "RSLNV"
output(i, 4) = "RSVNK"
output(i, 5) = "VTR appeared thrice"
End Select
i = i + 1
Next
With Sheet1
.Cells.Clear
.Range("A1").Resize(UBound(output, 1), UBound(output, 2)).Value = output
.Columns.AutoFit
End With
End Sub
Private Function Exists(col As Collection, key As String) As Boolean
Dim v As Variant
On Error Resume Next
v = col(key)
On Error GoTo 0
Exists = Not IsEmpty(v)
End Function