利用VBA寻找重复选项.docxVIP

一、函数1、将公式 =IF(ISERROR(VLOOKUP(B2,B$1:B1,1,0)),,重复) 在数据区域后空白列第二行，光标对准右下角成“+”状时将公式拖（或双击）下去，效果如图：2、=COUNTIF(A:A,A1)，大于1 的重复。二、VBA找出一列或一行中没有的重新列出来，用全部的去循环对比需要排除的。Sub 测试()Dim rng As Range, rngs As Range, k%, a, b 定义数据类型：设置对range单元格的引用，和定义K为整形变量，a,b为变体变量。For Each rng In [a2:a6] for each外循环的范围 a = rng.Value For Each rngs In [b2:b4] for each内循环的范围 b = rngs.Value If rng = rngs Then GoTo 100 跳出外循环 End If Next rngs k = k + 1 Cells(k + 1, c) = rng 把没等的内容复制到新的一列中100:Next rngEnd Sub找出重复的并列出次数：Sub 统计()y1 = 1 开始列为A列（在EXCEL中，A列的列号为1）y2 = 4 结束列为D列（在EXCEL中，D列的列号为4）x = 2n1 = 255 辅助列n2 = y2+2 结果显示列，结果显示在源数据列的右侧，中间间隔一列。For i = y1 To y2s = Cells(65536, y1).End(xlUp).Row 各列数据的数量Range(Cells(1, i), Cells(s, i)).Copy Cells(x, n1) 把所有数据复制到辅助列中x = x + sNextCells(1, n1) = 数据: Cells(1, n2 + 1) = 次数使用“高级筛选”功能将不重复数据显示在“结果显示列”中Columns(n1).AdvancedFilter 2, , Cells(1, n2), 1s1 = Cells(65536, n2).End(xlUp).Row下面代码用COUNTIF函数统计重复次数For i = 2 To s1Cells(i, n2 + 1) = WorksheetFunction.CountIf(Columns(n1), Cells(i, n2))Next消除辅助列内容Columns(n1) = End SubSub 按指定次数重复数据()Dim Rng, Arr()Dim i As Integer, j As Integer, k?? As IntegerDim LastRow As Integer, Total As IntegerLastRow = [A65536].End(xlUp).Row 从A列最后一行向上找，找到有数据的行为止Total = Application.WorksheetFunction.Sum(Range(B2:B LastRow))Rng = Range(A1:B LastRow)ReDim Arr(1 To Total, 1 To 1) 重新定义数组ARR,调整第一维下标从1起到20止,第二维下标从1起到11止For i = 2 To UBound(Rng, 1) For j = 1 To Rng(i, 2) k = k + 1 Arr(k, 1) = Rng(i, 1) NextNextRange(D2).Resize(k, 1).Value = ArrEnd SubSub test() Dim ar Dim d As Object Dim i As Integer, j As Integer Set d = CreateObject(scripting.dictionary) ar = Range(a1).CurrentRegion For j = 1 To 3?For i = 2 To UBound(ar)?If ar(i, j) Then d(ar(i, j)) = ?End If?Next Next Range(d2:d65536).Clear Range(d2).Resize(d.Count, 1).NumberFormatLocal = @ Range(d2).Resize(d.Count, 1) = Application.Transpose(d.keys)End SubSub Iterance()Dim i As Long? ?定义循环变量i类型为长整型For i = 1 To 10 循环10次 If Application.CountIf(Range(A1:A10), Cells(i, 1)) 1 Then Cells(i, 1).Font.Color =