Hi,

I am writing a macro to search for duplicates. I want to be able to apply
an auto filter and then for it search within those records. When I apply the
filter it still searches through the excluded cells. Is there anyway around
this.

My code is:

Dim SearchRange As Range
Dim EndRange As Range
Set EndRange = column.Find("")
Set SearchRange = Range(column.Cells(3), EndRange.Offset(1))

Selection.AutoFilter Field:=1, Criteria1:="Server"

Dim Book As Workbook
Dim OutputSheet As Worksheet
Set Book = Application.ActiveWorkbook
Book.Worksheets.Add After:=Book.Worksheets(1)
Set OutputSheet = Book.Worksheets(2)
OutputSheet.Name = "Test"
Dim OutputLine As Range
Set OutputLine = OutputSheet.Range("A1:D1")

For Each valuecell In SearchRange

If OutputSheet.Range("C:C").Find(valuecell.Value) Is Nothing _
And Not valuecell.Value = "" And Not valuecell.Value = "Unknown" _
And Not valuecell.Value = "Cable Management" _
And Not valuecell.Value = "UNKNOWN" Then
For Each searchcell In Range(valuecell.Offset(1), EndRange(-1))

If valuecell.Value = searchcell.Value Then

OutputLine.Cells(1).Value = valuecell.Address
OutputLine.Cells(2).Value = searchcell.Address
OutputLine.Cells(3).Value = valuecell.Value
OutputLine.Cells(4).Value = Sheets("Servers").Columns(1) _
.Cells(searchcell.Row).Value

Possibly adding

set SearchRange = SearchRange.SpecialCells(xlVisible)

--
Regards,
Tom Ogilvy


"wbiskit" wrote:

Hi,

I am writing a macro to search for duplicates. I want to be able to apply
an auto filter and then for it search within those records. When I apply the
filter it still searches through the excluded cells. Is there anyway around
this.

My code is:

Dim SearchRange As Range
Dim EndRange As Range
Set EndRange = column.Find("")
Set SearchRange = Range(column.Cells(3), EndRange.Offset(1))

Selection.AutoFilter Field:=1, Criteria1:="Server"

Dim Book As Workbook
Dim OutputSheet As Worksheet
Set Book = Application.ActiveWorkbook
Book.Worksheets.Add After:=Book.Worksheets(1)
Set OutputSheet = Book.Worksheets(2)
OutputSheet.Name = "Test"
Dim OutputLine As Range
Set OutputLine = OutputSheet.Range("A1:D1")

For Each valuecell In SearchRange

If OutputSheet.Range("C:C").Find(valuecell.Value) Is Nothing _
And Not valuecell.Value = "" And Not valuecell.Value = "Unknown" _
And Not valuecell.Value = "Cable Management" _
And Not valuecell.Value = "UNKNOWN" Then
For Each searchcell In Range(valuecell.Offset(1), EndRange(-1))

If valuecell.Value = searchcell.Value Then

OutputLine.Cells(1).Value = valuecell.Address
OutputLine.Cells(2).Value = searchcell.Address
OutputLine.Cells(3).Value = valuecell.Value
OutputLine.Cells(4).Value = Sheets("Servers").Columns(1) _
.Cells(searchcell.Row).Value