Find , mark and filter duplicates at ListObject (column of Dynamic Table) VBA

Rita F

New Member
Joined
Jan 27, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi
I am trying to find, mark and filter duplicates at ListObject (column of Dynamic Table) VBA without success.
The Following script works great for the regular range, I made some changes and need it for the list object.
I will very much appreciate your assistance

Sub Duplicates()





ActiveSheet.Shapes("shape3").Select 'change to whatever your shape is called
If Selection.ShapeRange.Fill.Visible = msoFalse Then
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 40
Else

Selection.ShapeRange.Fill.Visible = msoFalse

End If


Dim Rng As Range
Dim cel As Range

'Test for duplicates in a single column
'Duplicates will be highlighted in red


Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))


For Each cel In Rng
If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then

cel.Interior.ColorIndex = 3
End If
Next cel

Range("B:J").Select

ActiveSheet.Range("$B$1:$J$1").AutoFilter Field:=1, Criteria1:=RGB(255, 0 _
, 0), Operator:=xlFilterCellColor
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.Range("$B$1:$J$1").AutoFilter Field:=9, Criteria1:="<>0", _
Operator:=xlAnd



End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Would this work for you for, say, the second column of the ListObject?

VBA Code:
Sub Dupes()
  With ActiveSheet.ListObjects(1).ListColumns(2).DataBodyRange
    .FormatConditions.AddUniqueValues
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub
 
Solution

Rita F

New Member
Joined
Jan 27, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Would this work for you for, say, the second column of the ListObject?

VBA Code:
Sub Dupes()
  With ActiveSheet.ListObjects(1).ListColumns(2).DataBodyRange
    .FormatConditions.AddUniqueValues
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub
Great Solution, Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,828
Messages
5,627,135
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top