Find Address of merged range VBA

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

I am trying to return the address of the top left cell in a merged
range. thsi is what I have so far and it is not even close to right.

Set Rng = Range("A52:BS52").Find(what:=ComboBox1.Text,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)

MsgBox Rng.MergeArea.Resize(1, 1).Address

Thanks,
Jay
 
Jay,

If Rng.MergeCells Then
MsgBox Rng.MergeArea.Cells(1, 1).Address
Else
MsgBox Rng.Cells(1, 1).Address
End If

HTH,
Bernie
MS Excel MVP
 
If Rng.MergeCells Then
MsgBox Rng.MergeArea.Cells(1, 1).Address
Else
MsgBox Rng.Cells(1, 1).Address
End If

HTH,
Bernie
MS Excel MVP
Bernie,
After closer inspection it is nto finding the value from the combobox1
in merged cells. I realize now that I have to tell it to find in rows
52 through 54. Then it works perfectly. You got me pushed in the
right direction though.

Thanks,
Jay

If UCase(Right(Left(ComboBox1.Text, 5), 3)) = "RUN" Or _
ComboBox1.Text = "UpComing Kluge" Then
Set Rng = Range("A52:BS54").Find(what:=ComboBox1,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)
Else
Set Rng = Range("A2:FK2").Find(what:=ComboBox1,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)
End If
 
Back
Top