我需要在一個范圍內隱藏可見的重復單元格。
使用 AdvancedFilter，是的，它隱藏了重復的單元格（整行），但它也顯示了相應范圍內的所有隱藏行。
我嘗試使用SpecialCells(xlCellTypeVisible)方法，但出現以下錯誤：
運行時錯誤“1004”：資料庫或表范圍無效。
如果不適合使用 AdvancedFilter，還有哪些其他可能的方法？
一如既往，感謝您的所有幫助。
Sub Hide_Visible_Duplicate_Cells()
Dim ws As Worksheet, arng As Range, LastR As Long
Set ws = ThisWorkbook.ActiveSheet
LastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set arng = ws.Range("A1:A" & LastR)
arng.SpecialCells(xlCellTypeVisible).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=arng, Unique:=True
End Sub
uj5u.com熱心網友回復：
請嘗試下一個改編的代碼。它使用字典來檢測要隱藏的行（僅在創建字典鍵之后）并Union為各個單元格設定范圍。最后，EntireRow這個范圍將被隱藏：
Sub Hide_Visible_Duplicate_Cells()
Dim ws As Worksheet, arng As Range, LastR As Long
Dim C As Range, UnRng As Range, dict As New Scripting.Dictionary
Set ws = ThisWorkbook.ActiveSheet
LastR = ws.Range("A" & ws.rows.count).End(xlUp).row
On Error Resume Next 'just for the (improbable) case when no cell exist in the respective range
Set arng = ws.Range("A1:A" & LastR).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If arng Is Nothing Then Exit Sub
For Each C In arng.cells
If Not dict.Exists(C.Value) Then
dict.Add C.Value, vbNullString 'keep the first occurrence
Else
addToRange UnRng, C 'create a Union range for the next occurrences
End If
Next C
'hide the rows at once:
If Not UnRng Is Nothing Then UnRng.EntireRow.Hidden = True
End Sub
Sub addToRange(rngU As Range, rng As Range) 'Add to the Union range...
If rngU Is Nothing Then
Set rngU = rng
Else
Set rngU = Union(rngU, rng)
End If
End Sub
請在測驗后發送一些反饋。
編輯：
可以從另一個呼叫下一??個建議的解決方案Sub：
Sub Hide_Visible_Dup_Cells(procRng As Range)
Dim arng As Range, C As Range, UnRng As Range, dict As Object
On Error Resume Next
Set arng = procRng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If arng Is Nothing Then Exit Sub
Set dict = CreateObject("Scripting.Dictionary") 'no need of reference...
For Each C In arng.cells
If Not dict.Exists(C.Value) Then
dict.Add C.Value, vbNullString
Else
addToRange UnRng, C
End If
Next C
If Not UnRng Is Nothing Then UnRng.EntireRow.Hidden = True
End Sub
對于上述情況，它可以稱為：
Sub tesHide_Visible_Dup_Cells()
Dim ws As Worksheet, rng As Range, LastR As Long
Set ws = ThisWorkbook.ActiveSheet
LastR = ws.Range("A" & ws.rows.count).End(xlUp).row
Set rng = ws.Range("A1:A" & LastR)
Hide_Visible_Dup_Cells rng
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/521981.html