Find Range Borders (Cell Addresses) In VBA
 

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

Find Range Borders (Cell Addresses) In VBA
 

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
--
If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

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

Find Range Borders (Cell Addresses) In VBA
 

XClent function Jacob!
Thanx

"Jacob Skaria" wrote:

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
--
If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

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

Find Range Borders (Cell Addresses) In VBA
 

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
?
?
?

"Jacob Skaria" wrote:

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
--
If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

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

Find Range Borders (Cell Addresses) In VBA
 

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).A ddress, ":", ",")
End If
GetAddressString = Mid(GetAddressString, 2)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

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
?
?
?

"Jacob Skaria" wrote:

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
--
If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

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

Find Range Borders (Cell Addresses) In VBA
 

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

On 15 Maj, 08:52, FARAZ QURESHI
wrote:
I have a selected range, say X5:AC99. What piece of code would return a msgbox:
"The active range borders a
$X$5, $AC$5, $X$99 & $AC$99"

Find Range Borders (Cell Addresses) In VBA
 

Thanx Jarek!

XClent!

Never had the idea of using Cells().Address!

"Jarek Kujawa" wrote:

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

On 15 Maj, 08:52, FARAZ QURESHI
wrote:
I have a selected range, say X5:AC99. What piece of code would return a msgbox:
"The active range borders a
$X$5, $AC$5, $X$99 & $AC$99"

Find Range Borders (Cell Addresses) In VBA
 

welcome!
pls click Yes if it helped

On 15 Maj, 10:44, FARAZ QURESHI
wrote:
Thanx Jarek!

XClent!

Never had the idea of using Cells().Address!



"Jarek Kujawa" wrote:
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

On 15 Maj, 08:52, FARAZ QURESHI
wrote:
I have a selected range, say X5:AC99. What piece of code would return a msgbox:
"The active range borders a
$X$5, $AC$5, $X$99 & $AC$99"- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Find Range Borders (Cell Addresses) In VBA
 

use Long rather than Integer in the variable declaration



On 15 Maj, 10:44, FARAZ QURESHI
wrote:
ThanxJarek!

XClent!

Never had the idea of using Cells().Address!



"JarekKujawa" wrote:
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

On 15 Maj, 08:52, FARAZ QURESHI
wrote:
I have a selected range, say X5:AC99. What piece of code would return a msgbox:
"The active range borders a
$X$5, $AC$5, $X$99 & $AC$99"- Ukryj cytowany tekst -

- Pokaż cytowany tekst -