MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

returning the address of a highlighted area

  Asked By: Karina    Date: Mar 11    Category: MS Office    Views: 917

If I have an area of a spreadsheet highlighted (specifically it is
four segments each seperated by a blank and the number of rows in
each segment is different all the time) so in the code I have
started by highlighting row 6, then have used cntrl shft down arrow 4
times. It looks like this:

Range(selection, selection.End(xlDown)).Select
Range(selection, selection.End(xlDown)).Select
Range(selection, selection.End(xlDown)).Select
Range(selection, selection.End(xlDown)).Select

I want a bit of code that will return the address of the highlighted
range in the following format: A6:x30 so that then I can make it the
Print_area named range so that I can get the ever changing set of
data to print.

I would also be interested in a way to just set a highlighted region
as the print area without having stored the range address if anyone
has anything that does that.



1 Answer Found

Answer #1    Answered By: Norman Ray     Answered On: Mar 11

I'd be very surprised if there aren't much ways to do this, but this
is what works

To get the range:

Sub GetRangeAddress()
Dim S As Range
Dim letters As String, FirstLet As String, SecondLet As String
Dim ULCol As String, ULCell As String, LRCol As String, LRCell As
Dim c As String, r As Integer

Set S = Selection

'fast and easy (or is it hard and difficult?) conversion
letters = " A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"
For i = 1 To 7
FirstLet = Mid$(letters, i * 2, 1)
For j = 1 To 26
SecondLet = Mid$(letters, j * 2, 1)
letters = letters & FirstLet & SecondLet
Next j
Next i
'get upper left of selection
If S.Column <= 26 Then _
ULCol = Mid$(letters, S.Column * 2, 1) _
Else _
ULCol = Mid$(letters, S.Column * 2 - 1, 2)
ULCell = ULCol & S.Row
'get last cell in selection.
For Each cl In S
r = cl.Row
c = cl.Column

If c <= 26 Then _
LRCol = Mid$(letters, c * 2, 1) _
Else _
LRCol = Mid$(letters, c * 2 - 1, 2)
LRCell = LRCol & r
ThisRange = ULCell & ":" & LRCell
End Sub

To just print  the selection, unless I'm mising something:

Selection.PrintOut Copies:=1

Didn't find what you were looking for? Find more on returning the address of a highlighted area Or get search suggestion and latest updates.