|
hongqiqqq 发表于 2013-12-31 07:25
如果不考虑这一个问题,其他的能处理吗? - Sub test()
- Dim arr, i As Long, str$
- Dim objDic As Object
- Set objDic = CreateObject("scripting.dictionary")
- With Sheet1
- .Columns(2).Copy Sheet2.Range("b1")
- arr = .Range("d1:f" & .Cells(Rows.Count, "d").End(xlUp).Row)
- End With
-
- For i = 1 To UBound(arr)
- str = arr(i, 1)
- If str Like "?*--*" Then
- str = Left(str, InStr(arr(i, 1), "--") - 1)
- ElseIf arr(i, 1) Like "--*" Then
- str = ""
- End If
- str = Replace(myreplace(str), "郑州市", "")
- arr(i, 1) = str
- objDic(str) = ""
- Next
- With Sheet4
- .Columns(1).Clear
- '.Range("a1").Resize(UBound(arr)).Value = arr
- '.Columns(1).RemoveDuplicates 1, xlNo
- '10自带的去重复功能
- If objDic.Count Then
- .Range("b1").Resize(objDic.Count).Value = WorksheetFunction.Transpose(objDic.keys)
- End If
- '字典去重复,写在B1列了。
- End With
- Set objDic = Nothing
- MsgBox "完成"
- End Sub
- Function myreplace(str) As String
- Dim temp As String * 1
- Dim i As Long
- For i = 1 To Len(str)
- temp = Mid(str, i, 1)
- If Not temp Like "“,。?、·【】;!”"",.?[]!;" Then
- myreplace = myreplace & temp
- End If
- Next
- End Function
复制代码 |
|