VBA to identify top left cell of a selected range

[exceluser2007]

Hi All,

I'm curious to know, that if a user has selected a range, how do you, in VBA, identify the:

1. Top left cell
2. Bottom left cell
3. Top right cell
4. Bottom right cell

For example if user has selected the range B5:M30, then we would want to identify in the macro:

1. Top left cell = B5
2. Bottom left cell = B30
3. Top right cell = M5
4. Bottom right cell = M30

Any help appreciated.

 

[jindon]

Then

If Not TypeOf Selection Is Range Then Exit Sub

 

[exceluser2007]

Nice, didn't know the "typeof" command in VBA.

Cheers

 

[ljo00]

Just a quick question to revive an old (but useful) thread - in Tom's original code, he declares variables with $, & and % modifiers in the dim statement. What is this used for?

Thanks!

Sub Test2()
Dim cell As Range, MergeVal$
Dim TR&, BR&, LC%, RC%
With Selection
TR = .row
BR = .Rows.Count + .row - 1
LC = .Column
RC = .Columns.Count + .Column - 1
MsgBox _
"Top row: " & TR & vbCrLf & _
"Bottom row: " & BR & vbCrLf & _
"Left Column: " & LC & vbCrLf & _
"Right column: " & RC
End With

End Sub

 

[Tom Urtis]

The $ is an abbreviated was of saying "as String".

Those abbreviation characters have been around for awhile, here is a general overview of variable types, their abbreviation characters, and their description. The intervening dots are my attempt to enhance readability for the fields on this html page.

Type...............Memory....Character...........Description
Byte...............1 byte....none................Positive whole number ranging from 0 through 255 that can be represented as a binary value.
Boolean............2 bytes...none................True or False value
Integer............2 bytes...%...................Whole numbers ranging from -32,768 through 32,767.
Long...............4 bytes...&...................Whole numbers ranging from -2,147,483,648 through 2,147,483,647.
Single.............4 bytes...!...................Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.
Double.............8 bytes...#...................Double-precision floating-point number ranging from -1.79769313486232E308 to 1.79769313486232E308.
Currency...........8 bytes...@...................Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Date...............8 bytes...none................Represents dates from January 1, 100 through December 31, 9999.
Object.............4 bytes...none................An instance of a class or object reference.
String.............10 bytes + 1 byte per char...$...Series of any ASCII characters.
String (fix len)...length of string..............none...Series of any ASCII characters, of a pre-defined length.
Variant............min 16 bytes..................none...Any kind of data except fixed-length String data and user-defined types.
<!-- / message --><!-- sig -->

 

[ljo00]

Very helpful! Thanks for the quick reply.

 

[silicon_beaver]

Wonderful.

I just re-use the code.

Thank you Tom and Jindon