.End(xlToLeft) only finds in unhidden cells?

[yky]

Does .End(xlToLeft) only finds in unhidden cells?

I have the following code which finds a date, hides all other dates, and shows only the date it found. The code worked fine the first time it ran (none of the dates is hidden). After that, it could only find dates after the first date it found. For example, if the first time it found July 11th, 2011. The second time and after, the code can only find dates before July 11th.

I believe this is because after the first found, only July 11th was shown. The second time around, the range was set to July 11th and not after.

I guess I can hard code the range to range([A2],[ABZ2]) but is there a way to use .End(xlToLeft) and still make the code work? Thanks.

=============================================
Set rng2 = ActiveSheet.Range([A2], [ABZ2].End(xlToLeft))

Set r = rng2.Find(edate, LookIn:=xlFormulas)
If Not r Is Nothing Then
MsgBox (edate & " found")
Range([e3], [ABZ3].End(xlToLeft)).EntireColumn.Hidden = True
Range(Cells(3, r.Column()), Cells(3, r.Column() + 3)).EntireColumn.Hidden = False
Else
MsgBox (edate & " not found")

End If

 

[Marcelo Branco]

Hi,

Not sure if i understood correctly, but maybe introducing this
Range([A2], [ABZ2]).EntireColumn.Hidden = False

before
Set rng2 = ActiveSheet.Range([A2], [ABZ2].End(xlToLeft))

HTH

M.

 

[yky]

Hi,

Not sure if i understood correctly, but maybe introducing this
Range([A2], [ABZ2]).EntireColumn.Hidden = False

before
Set rng2 = ActiveSheet.Range([A2], [ABZ2].End(xlToLeft))

HTH

M.

Thank you for the prompt reply.

That is what I ended up doing - unhide all the columns, do the find, hide all the columns, show only the columns that correspond to the date found.

I wish I don't have to unhide all the columns.

 

[Marcelo Branco]

Thank you for the prompt reply.

That is what I ended up doing - unhide all the columns, do the find, hide all the columns, show only the columns that correspond to the date found.

I wish I don't have to unhide all the columns.

You are welcome.

Yes,... i also tried without unhiding all columns, but could not find a way...

M.