finding cells excluded by autofilter
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
|