.Find method on grouped columns

[osx99]

An extract of my code below works fine if column EV is visible. However, I have this grouped together with other columns to hide these columns from the user. The problem is the code below then returns nothing when the column is grouped and hidden

I could unhide the column as part of the code but is there a more elegant way to find in hidden/grouped cells?

With Worksheets(1).Range("ev1:ev3500")

            Set rng = .Find(What:=Me.OLEObjects(comboval).Object.Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then


            End if

End with

Thanks,
Os

 

[combo]

The 'Search' works fine for hidden cells.
If you merged cells, some data (all except top-left cell) were lost.
The problem could be caused by the cell specified in 'After'. You search Range("ev1:ev3500"), starting after last cell (.cells.count returns number of cells in searched range).

combo

 

[osx99]

I checked for merged cells and there are none

I then tried

With Worksheets(1).Range("ev1:ev3500")
            Set findrange = Range("ev1")
            Set rng = .Find(What:=Me.OLEObjects(comboval).Object.Value, _
                            After:=findrange, _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then

and

With Worksheets(1).Range("ev1:ev3500")
            Set rng = .Find(What:=Me.OLEObjects(comboval).Object.Value, _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then

And both fail when EV is grouped but work fine when it's ungrouped?

 

[SkipVought]

In that case, ungroup, find, regroup in your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}