I have what I hope is a simple looping question: I'm trying to
filter each of many worksheets in a workbook for the unique values
in a particular column, and then "unfilter" the whole workbook. My
"unique" function enters an infinite loop, I think. Any clues?

Sub unique()

Dim ws As Worksheet

For Each ws In Worksheets
'Columns("B:B").Select
ws.UsedRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
Next

End Sub


Sub ununique()

Dim ws As Worksheet

For Each ws In Worksheets
ActiveSheet.ShowAllData
Next

End Sub

Hi
This worked for me
Sub unique()
Dim ws As Worksheet
For Each ws In Worksheets
ws.UsedRange.AdvancedFilter Action:=xlFilterInPlace,
unique:=True
Next
End Sub


Sub ununique()
Dim ws As Worksheet
For Each ws In Worksheets
ws.ShowAllData
Next
End Sub

this was tested on two sheets which had the data
Name
Paul
John
Paul

in column A. Perhaps your problem is with the data? Do your columns
have headers?

regards
Paul


Person wrote:

I have what I hope is a simple looping question: I'm trying to
filter each of many worksheets in a workbook for the unique values
in a particular column, and then "unfilter" the whole workbook. My
"unique" function enters an infinite loop, I think. Any clues?

Sub unique()

Dim ws As Worksheet

For Each ws In Worksheets
'Columns("B:B").Select
ws.UsedRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
Next

End Sub


Sub ununique()

Dim ws As Worksheet

For Each ws In Worksheets
ActiveSheet.ShowAllData
Next

End Sub

Not sure about the infinite loop, but your ununique() subroutine is not
going
to work, as you are referencing ActiveSheet instead of your loop
variable.

Scott

Person wrote:
I have what I hope is a simple looping question: I'm trying to
filter each of many worksheets in a workbook for the unique values
in a particular column, and then "unfilter" the whole workbook. My
"unique" function enters an infinite loop, I think. Any clues?

Sub unique()

Dim ws As Worksheet

For Each ws In Worksheets
'Columns("B:B").Select
ws.UsedRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
Next

End Sub


Sub ununique()

Dim ws As Worksheet

For Each ws In Worksheets
ActiveSheet.ShowAllData
Next

End Sub

Criteria Range, you will see that Unique is not listed
as an acceptable criteria.

That is because a criteria range is not required if you specify that the
extract or filter is to return only unique values. From reading your
response, it doesn't appear that you are aware of this.

Also, the criteria should be listed in a range on
a worksheet and in the code you would refer to that range e.g.
CriteriaRange:=Range("B2").

Not if unique values are required. If a criteria range is desired, it
should be at least two rows in depth.


Now, with all that said, I believe you would be
better off using loop that matches one range to another to find the unique
items and creates a separate list.

That would be true if the Advanced filter didn't do all that for you with
one command.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
If you use Excel Help to find "Filter by using advanced criteria" and then
read the section for Criteria Range, you will see that Unique is not
listed
as an acceptable criteria. Also, the criteria should be listed in a range
on
a worksheet and in the code you would refer to that range e.g.
CriteriaRange:=Range("B2"). Now, with all that said, I believe you would
be
better off using loop that matches one range to another to find the unique
items and creates a separate list. Think about it and if you need help,
re-post, giving a sample of the data layout on the worksheet and what you
want to use to determine unique items, i.e. part number, name, color,
size,
date, etc. Don't get discouraged, it takes time.

"Person" wrote:

I have what I hope is a simple looping question: I'm trying to
filter each of many worksheets in a workbook for the unique values
in a particular column, and then "unfilter" the whole workbook. My
"unique" function enters an infinite loop, I think. Any clues?

Sub unique()

Dim ws As Worksheet

For Each ws In Worksheets
'Columns("B:B").Select
ws.UsedRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
Next

End Sub


Sub ununique()

Dim ws As Worksheet

For Each ws In Worksheets
ActiveSheet.ShowAllData
Next

End Sub