| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Borders for range | Excel Discussion (Misc queries) | |||
| Sorting a range loses formatted borders | Excel Discussion (Misc queries) | |||
| Find cells without borders | Excel Discussion (Misc queries) | |||
| find last cell in range with data, display cell address | Excel Worksheet Functions | |||
| Find a Cell Value In a Range | Excel Worksheet Functions | |||
Linear Mode
