Example: Generating a Range String from numbers.

[Mac Lingo]

This is a coding example of how to make a range string from numbers.

Function RangeString(Row1, Col1, Row2, Col2)
' Return Range String from Arguments.
' No checking for reversed range specs.

RangeString = Nr2Col(Col1) & Row1 & ":" & Nr2Col(Col2) & Row2
End Function

Function Nr2Col(NR)
' Turn NR into Excel Column Designator.
If NR <= 26 Then
Nr2Col = Chr(64 + NR)
Else
Nr1 = Chr(64 + Int(NR / 26))
Nr2 = Chr(65 + NR Mod 26)
Nr2Col = Nr1 & Nr2
End If
End Function

RangeString(1,1,2,5) returns "A1:E2".

Let me know if you find this helpful. Thanks
Mac@ SV-CaliforniaGirl.Com

 

[JE McGimpsey]

While your logic and coding are fine, you may find using a built-in
method more efficient:

Public Function RangeString( _
Row1 As Long, Col1 As Long, _
Row2 As Long, Col2 As Long) As Variant
On Error GoTo ErrHandler

RangeString = Range(Cells(Row1, Col1), _
Cells(Row2, Col2)).Address(False, False)

Exit Function
ErrHandler:
RangeString = CVErr(xlErrValue)
End Function

 

[Mac Lingo]

Thanks, JE McGimpsey,

Much more elegant solution, and it handles the incorrect specification
problem as well.

But what is the "Address(False,False)" for?

Mac

 

[Tom Ogilvy]

In your code module, highlight address and hit F1.