第1个回答 2012-04-01
'*** 在工作页面(例如:sheet1)内加个“命令按钮”其代码如下:
Private Sub CommandButton1_Click()
Macro1
End Sub
'****** 模块代码如下:
Sub Macro1()
For I = 1 To 65535
If Len(ActiveSheet.Cells(I, 2).Value) = 0 Then
Exit For
End If
Next I
I = I - 1
For j = 1 To 65535
If Len(ActiveSheet.Cells(j, 1).Value) = 0 Then
Exit For
End If
Next j
W = 0
For K = 1 To j - 1
N = 0
For H = 1 To I + W
If ActiveSheet.Cells(K, 1).Value = ActiveSheet.Cells(H, 2).Value Then
Exit For
Else
N = N + 1
End If
Next H
If N = I + W Then
W = W + 1
ActiveSheet.Cells(H, 2).Value = ActiveSheet.Cells(K, 1).Value
End If
Next K
End Sub
第2个回答 2012-03-31
'AB列最后一行行号
LA = Range("a65536").End(xlUp).Row
LB = Range("b65536").End(xlUp).Row
For I = 2 To LA
'获取A列当前单元格值
C = Cells(I, 1)
'用COUNTIF统计B列中C出现的次数,若为0则添加
If Application.WorksheetFunction.CountIf(Range(Cells(1, 2), Cells(LB, 2)), C) = 0 Then
LB = LB + 1
Cells(LB, 2) = C
End If
Next