.Find won't find cells hidden with AutoFilter

[FotS]

Hey guys,

I have a table that is setup with an AutoFilter so it's a little easier to work with considering the range of data that's in it. I'm trying to setup some code for a couple of the columns that when there is a change (data entered), it searches the whole column to verify it isn't a duplicate entry. The issue I'm coming across is that .Find will not locate a duplicate if that duplicate is currently "hidden" from the AutoFilter. Searching online I found people saying to set the LookIn argument to xlFormulas, but that doesn't work.

The code works perfectly, otherwise.

Oh, and using Excel 2003, SP3.

Any ideas?

 

[FotS]

Wow threads drop fast here....

Anyway, wanted to ask another question here concerning AutoFilters and the cells it hides....

Is there any way (aside from manually doing checks to see if the cell's value meets the AutoFilter's criteria) to tell if a cell is hidden (not visible) by the AutoFilter? Doing range.Hidden returns an error "Unable to get the Hidden property of the Range class."

 

[FotS]

Bump from page 9

 

[Marcelo Branco]

Hi,

To check if a cell is hidden (not visible) maybe something like

Dim mydata as Range, aCell as Range
 
Set mydata = Range("A1:A10")
 
 For Each aCell In mydata
        If aCell.EntireRow.Hidden Then MsgBox aCell.Row
 Next aCell

HTH

M.

 

[FotS]

Thank you sir! That did it. It's interesting that one would be forced to check via the EntireRow, though....

 

[Peter_SSs]

It's interesting that one would be forced to check via the EntireRow, though....

Not surprising really though since you cannot hide a single cell (or any range) without hiding the whole row or the whole column (or both).