Find Range Borders (Cell Addresses) In VBA

F

FARAZ QURESHI

I have a selected range, say X5:AC99. What piece of code would return a msgbox:
"The active range borders are:
$X$5, $AC$5, $X$99 & $AC$99"
 
J

Jacob Skaria

Paste the below function and call it as..

Msgbox "The active borders are " & GetaddressString(Range("A1:B10"))

Function GetAddressString(varRange As Range) As String
Dim varTemp As Range
For Each varTemp In varRange.Columns
GetAddressString = GetAddressString & "," & Replace(varTemp.Address, ":", ",")
Next
GetAddressString = Mid(GetAddressString, 2)
End Function
 
F

FARAZ QURESHI

XClent function Jacob!
Thanx

Jacob Skaria said:
Paste the below function and call it as..

Msgbox "The active borders are " & GetaddressString(Range("A1:B10"))

Function GetAddressString(varRange As Range) As String
Dim varTemp As Range
For Each varTemp In varRange.Columns
GetAddressString = GetAddressString & "," & Replace(varTemp.Address, ":", ",")
Next
GetAddressString = Mid(GetAddressString, 2)
End Function
Click to expand...
 
F

FARAZ QURESHI

Sorry Jacob,
But upon applying it on my data, i.e. X5:AC99, result is not as expected. It
comes to
$X$5,$X$99,$Y$5,$Y$99,$Z$5,$Z$99,$AA$5,$AA$99,$AB$5,$AB$99,$AC$5,$AC$99
?
?
?
 
J

Jacob Skaria

Oops. .modified to suit your requirement...

Function GetAddressString(varRange As Range) As String
Dim varTemp As Range

GetAddressString = GetAddressString & "," & _
Replace(varRange.Columns(1).Address, ":", ",")
If varRange.Columns.Count > 1 Then
GetAddressString = GetAddressString & "," & _
Replace(varRange.Columns(varRange.Columns.Count).Address, ":", ",")
End If
GetAddressString = Mid(GetAddressString, 2)
End Function


If this post helps click Yes
 
J

Jarek Kujawa

use this macro:

Sub cus()
Dim min_row As Integer
Dim rows_count As Integer
Dim min_col As Integer
Dim cols_count As Integer
Dim Borders As String

min_row = Selection.Row
rows_count = Selection.Rows.Count
min_col = Selection.Column
cols_count = Selection.Columns.Count
Borders = Cells(min_row, min_col).Address & "," & Cells(min_row,
min_col + cols_count - 1).Address & "," & Cells(min_row + rows_count -
1, min_col).Address & "," & Cells(min_row + rows_count - 1, min_col +
cols_count - 1).Address

MsgBox "The active borders are " & Borders

End Sub
 
Top