How To: VBA Cells.Find(..... also look in hidden columns?

[Kevin McCartney]

Hi,
I'm try to find a value in a specific column. The column
itself is hidden.

I use the command, rgeUniqueID.Cells.Find
(What:=strFindUniqueID, LookIn:=xlValues,
SearchOrder:=xlRows, MatchCase:=False)

The code works fine when the column is not hidden. The
point is that I don't want to hide and show the column
each time a need to search for a Unique ID.

Thanks for your help
regards
KM

 

[Tom Ogilvy]

If you replace xlValues with xlFormulas, it works for me with a hidden
column.

 

[Tim Zych]

Hmmm, it doesn't see to like the xlValues parameter.

Trying to replicate the approach:

rngUniqueID.Cells.SpecialCells(xlCellTypeConstants). _
Find(What:=strFindUniqueID, LookIn:=xlFormulas, _
SearchOrder:=xlRows, MatchCase:=False)

seems to work for hidden columns.

Specialcells has a disadvantage in that it can't be used in a protected
sheet. Reprotecting the sheet with userinterfaceonly will work around that
limitation if applicable.

 

[Kevin McCartney]

To banana it works
Cheers buddy
regards
KM

-----Original Message-----
If you replace xlValues with xlFormulas, it works for me with a hidden
column.

--
Regards,
Tom Ogilvy




.

 

[yehuda54]

Hmmm, it doesn't see to like the xlValues parameter.

Trying to replicate the approach:

rngUniqueID.Cells.SpecialCells(xlCellTypeConstants). _
Find(What:=strFindUniqueID, LookIn:=xlFormulas, _
SearchOrder:=xlRows, MatchCase:=False)

seems to work for hidden columns.

Specialcells has a disadvantage in that it can't be used in a protected
sheet. Reprotecting the sheet with userinterfaceonly will work around that
limitation if applicable.


"Kevin McCartney" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I'm try to find a value in a specific column. The column
> itself is hidden.
>
> I use the command, rgeUniqueID.Cells.Find
> (What:=strFindUniqueID, LookIn:=xlValues,
> SearchOrder:=xlRows, MatchCase:=False)
>
> The code works fine when the column is not hidden. The
> point is that I don't want to hide and show the column
> each time a need to search for a Unique ID.
>
> Thanks for your help
> regards
> KM
>

I works, however if there is a filter on the column it fails -- Any ideas how to find hidden columns even with a a filter on a column active?