If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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" |
#2
|
|||
|
|||
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" |
#3
|
|||
|
|||
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" |
#4
|
|||
|
|||
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" |
#5
|
|||
|
|||
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" |
#6
|
|||
|
|||
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" |
#7
|
|||
|
|||
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" |
#8
|
|||
|
|||
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 - |
#9
|
|||
|
|||
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 - |
Thread Tools | |
Display Modes | |
|
|