Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding cells excluded by autofilter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find data excluded from a list in excel? | Excel Discussion (Misc queries) | |||
Is there a function that allows me to list the names of excluded d | Excel Worksheet Functions | |||
HOW DO I EXCLUDED A FIGURE FROM A CONDITIONAL FORMAT???? | Excel Worksheet Functions | |||
How to create a formula where high and low numbers are excluded | Excel Discussion (Misc queries) | |||
Finding row count and filtered rows returned by Autofilter | Excel Programming |