Solved: Range(????).Find on Hidden Columns

[CBrine]

I don't use the find very often programmatically within VBA, but decided to do this with a project I'm working on, since I know it's faster then cycling through cells.
I have a summary with unique values based on 2 columns, which I create in Column A by linking the two other columns together.

A......B......C
12.....1......2
13.....1......3
21.....2......1

etc....
I then hide column A so that it doesn't show up in the column, since I don't want the user to see the unique index I'm using.

The problem occurs when I try and execute a find on the hidden column. It doesn't seem to work?
Has anyone found a way around this, other then unhiding, then re-hiding the column? or am I stuck with it?

Thanks
Cal

[mdmackillop]

Hi Cal
You could try Match, which will return the index of the matched cell.
[vba]
Option Explicit
Sub Macro1()
Dim Rng As Range
Dim MyStr As String
Dim Test As Long
MyStr = InputBox("Text to find")
Set Rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Test = Application.WorksheetFunction.Match(MyStr, Rng.Value)
MsgBox Test
End Sub

[/vba]

[CBrine]

md,
Did some quick testing of the processing times using
Match
Find
Cell Cycling
It looks like the vba match wins hands down at about 1/40th the time it takes for the other two methods. It's quick. Think I will go with it.

Thanks md.

[mdmackillop]

HTH,
But also check Match in Help for the MatchType to make sure you get the answer your looking for.

[CBrine]

md,
Thanks for the head's up. I was looking for an exact match(0). Surprised it doesn't default to that.

Cal