Dim m!, n!, t$, Exist As Boolean, i As Range, j As Range, r As Range, rj As Range, a
t = "C5:H20"
For Each j In Range(t).Columns
Set rj = Intersect(j, Range(t))
ReDim a(rj.Count - 1)
n = 0
For Each i In rj
If WorksheetFunction.CountIf(rj, i) >= 3 Then
Exist = False
For m = 0 To UBound(a) - 1
If a(m) = i Then Exist = True: Exit For
Next
If Not Exist Then
a(n) = i.Value
n = n + 1
End If
End If
Next
rj.ClearContents
rj(1).Resize(UBound(a) - 1) = WorksheetFunction.Transpose(a)
Next
End Sub
附件请下载参考
有点不对
(C5:H20)这个范围删除重复内容，不管同不同列
现在是同列3个以上相同才保留1
【删除后同列内容往上移动】？
这如何解释？
如果是范围内删除，那保留的是哪一个？放在哪里？
如果是范围内删除，那保留的是哪一个？任意一个
【删除后同列内容往上移动】？对
好复杂的应用啊！
Sub keep3()Dim a, b, l!, m!, n!, t$, Exist As Boolean, i As Range, j As Range, r As Range, rj As Range, tj As Range
t = "C5:H20"
n = 0
ReDim a(n)
For Each j In Range(t).Columns
Set rj = Intersect(j, Range(t))
ReDim b(rj.Count - 1)
l = 0
Set tj = Range(Cells(rj(1).Row, rj(1).Column), Cells(Range(t).Row + Range(t).Rows.Count - 1, Range(t).Column + Range(t).Columns.Count - 1))
For Each i In rj
If WorksheetFunction.CountIf(tj, i) >= 3 Then
Exist = False
For m = 0 To UBound(a)
If a(m) = i Then Exist = True: Exit For
Next
If Not Exist Then
a(n) = i.Value: b(l) = i.Value
l = l + 1: n = n + 1
ReDim Preserve a(n)
End If
End If
Next
For m = UBound(b) To 0 Step -1
If b(m) <> "" Then
Cells(m + rj.Row, rj.Column) = b(m)
Else
Cells(m + rj.Row, rj.Column).Delete (xlShiftUp)
End If
Next
Next
End Sub
新的附件请参考
Sub sc()
Dim i, j, k, l
For i = 3 To 8
For j = 20 To 5 Step -1
For k = 5 To j - 1
If Cells(j, i) = Cells(k, i) Then
Application.CutCopyMode = False
Cells(j, i).Delete Shift:=xlUp
End If
Next
Next
Next
End Sub追问
代码不对
(C5:H20)这个范围删除重复内容，只保留一个重复的
删除后同列内容往上移动
Excel表格怎么用VBA来实现删除重复值的操作
1. 首先双击打开Excel表格，我们可以看到，A列中有一些重复值，下面我们就来操作删除重复项。2. 首先选择工具栏上的“开发工具”，然后选择左侧的“Visual Basic”。3.然后我们就进入VBA窗口下，双击左侧数据的工作表，接着在右侧就会弹出空白代码编辑区。4. 然后在空白区域输入代码：Sub 鱼木混猪()&#...
怎么通过宏VBA代码删除Excel中有重复数据的整行?
1、首先打开需要编辑的Excel表格，右键单击工作表的标签，选择打开“查看代码”。2、然后在弹出来的窗口中点击输入：Sub 删除重复行()Dim xRow As Integer Dim i As Integer xRow = Range("B65536").End(xlUp).Row For i = 2 To xRow For j = i + 1 To xRow If Cells(j, 2) = Cells...
excel 用VBA如何删除一列中的重复数据
6、按F5，运行代码，后返回工作表，会发现单元格中的重复字符已经被去掉。
怎么通过宏VBA代码删除Excel中有重复数据的整行?
1、进入EXCEL,ALT+F11进入VBA编辑器。2、在编辑区输入VBA语言Sub Macro1()， VBA 语言选择整行整列的语句，End Sub。3、在工作表中插入表单控件，并指定到宏Macro1。4、弹出的新界面中，再次点击”确定“。5、现在我们来删除整个表格中的重复数据（也叫重复行）：鼠标选中整个数据表格，在”数据“...
用VBA代码删除EXCEL中重复的十几行数据
If Cells(i, 1) = "总计" Then a = i + 1 If Cells(i, 1) = "日期" Then If a = "" Or a = i Then Else Rows(a & ":" & i - 1).Delete Shift:=xlUp End If ElseIf i = UsedRange.Rows.Count Then If a < i Then Rows(a & ":" & i).Delete Shift:=xlUp End...
求vba代码 有条件的合并单元格同时删除重复值保留唯一值用“+”连接...
要实现你描述的功能，可以使用VBA编写一个宏来处理Excel中的数据。以下是一个简单的VBA示例，它将根据A列的值合并C列的内容，并删除重复值，用“+”连接保留的唯一值。Sub MergeCellsAndCombineValues()Dim ws As Worksheet Dim dict As Object Dim key As Variant Dim lastRow As Long, i As Long...
Excel 如何用vba多列删除重复项
Sub 删除重复项()Dim arr, d Dim i As Integer, c As Range Set d = CreateObject("Scripting.Dictionary")arr = Application.InputBox("请用鼠标选择你需要操作的区域", Type:=8)For Each arr1 In arr d(arr1) = ""Next Set c = Application.InputBox("请用鼠标选择你放置的单元格"...
EXCEL VBA删除相同内容列
Sub s() n = Cells(Rows.Count, 11).End(3).Row k = Cells(n, Columns.Count).End(1).Column For i = 11 To k - 1 j = i + 1 Do While j <= k For x = 13 To n If Cells(x, i) <> Cells(x, j) Then GoTo xxx End If Next C...
excel删除相同内容
用VBA吧 Sub Delete() 'A列还是重复的都删除 Application.ScreenUpdating = False Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove x = Range("a56565").End(3).Row For i = 1 To x '循环行数 If Application.CountIf(Range("a1:a" & x), Cells(i, 1...
excel vba如何删除两个表重复行
Sub 删除重复行1()Dim i As Long Application.ScreenUpdating = False For i = Range("A65536").End(xlUp).Row To 3 Step -1 If WorksheetFunction.CountIf(Range("A2:A" & i), Cells(i, 1)) > 1 Then Cells(i, 1).EntireRow.delete End If Next Application.ScreenUpdating = True E...
