how to search the upper and lower of a range

[leungkong]

I want to search the upper and lower row of a range.
for example,
Range("B3:E10")
Upper = "B"
Lower = "E"

Thanks.

 

[Joel]

Why? There are usually much better ways of doing this rather than get the
letters. Explain the problem, maybe there is a better solution.


Sub test()

Set MyRange = Range("B3:E10")
FirstColNum = MyRange.Column
FirstColLetter = MyRange.Address
'remove $ sign
FirstColLetter = Mid(FirstColLetter, 2)
'remove rest of string, look for $
FirstColLetter = Left(FirstColLetter, InStr(FirstColLetter, "$") - 1)

SecondColNum = MyRange.Column + MyRange.Columns.Count - 1
SecondColLetter = MyRange.Address
'remove everything before colon
SecondColLetter = Mid(SecondColLetter, InStr(SecondColLetter, ":") + 1)
'remove $ sign
SecondColLetter = Mid(SecondColLetter, 2)
'remove rest of string, look for $
SecondColLetter = Left(SecondColLetter, InStr(SecondColLetter, "$") - 1)

End Sub

 

[Alan Beban]

I want to search the upper and lower row of a range.
for example,
Range("B3:E10")
Upper = "B"
Lower = "E"

Thanks.

What do you mean you want to "search it"? Loop through it? Find a
specific value in it? Something else?

Alan Beban

 

[leungkong]

Hi Joel,
very useful for "MyRange.Address", "MyRange.Columns.Count ",
"MyRange.Column", "InStr()" & "Mid()
Thanks so much.