Help with Excel macro recording and absolute addresses 2

[JohnCHolmes]

Surprise, surprise - bet nobody's ever had macro-record do this, huh? (Moreover, has anyone ever really wanted an absolute reference recorded?! Seriously - ever? See footnote). Well in Chapter 47 of the heartwarming story of these absolute references, today I would like to know how to extend the selection range. E.g.,

C5 to C6 is selected.
C5 to C9 is what I want to be selected.

I'm looking for something like
Selection.End = ActiveCell.Offset(3, 0)

which is invalid. Now Bless the macro recorder's heart, Range("C5:C9&quot.Select is not the solution, because what I want to do is push the end of the selection range out by 3 rows.

Thanks for your help on this, guys. I dread having to properly know how to program things, in order to program things LOL

[sub]Clueless (KLOO-less): Condition of the developer of Excel's record new macro.[/sub]

 

[DaleWatson123321]

Hi John,

I believe the following is what you're seeking...

Sub Extend_Range()
startcell = ActiveCell.Address
endcell = ActiveCell.Offset(3, 0).Address
rng = startcell & ":" & endcell
Range(rng).Select
'if you ALSO would like to assign a range name,
'the use the following line
'(use your own preference instead of "myrange&quot
Range(rng).Name = "myrange"
End Sub

Please advise as to how it "fits".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JohnCHolmes]

Yessirree, that'll do it.

That was a lot more work than I expected ... I was hoping for some kind of simple property adjustment ... grasping for
selection.rows = selection.rows +3
or even, if I really lucked out,
selection.count = selection.count + 3

And extra credit for subtly encouraging range naming
(Partial extra credit was available for "AND USE NAMED RANGES, OR YOU SUCK!&quot

 

[ratman]

Consider this:

Range(ActiveCell.Offset(3, 0).Address & ":" & _ActiveCell.Offset(3, 1).Address).Select

 

[JohnCHolmes]

Ratman, thank you for answering the Rat Reacon and arriving to save the day (Oops, wrong comic!) Your answer is pretty close to what Dale had, and his did hit the mark.

Below is a good adaptation, if anyone ever needs. Shown is the hardwired, quick Subroutine version; most might prefer using
Function ExtendSelRange(iNewSelRows As Integer, iNewSelCols As Integer
instead.

Sub ExtendSelRange() 'can be Function instead, w/ args
'extends Selected Range to a specific [resulting] # of rows & cols
Dim iNewSelRows As Integer, iNewSelCols As Integer
'iNewSelRows = 3: iNewSelCols = 6
iNewSelRows = 5: iNewSelCols = 1
Range(ActiveCell.Offset(0, 0).Address & ":" & ActiveCell.Offset(iNewSelRows - 1, iNewSelCols - 1).Address).Select
End Sub

 

[bryanbayfield]

You need the Resize method.

The following will do the job for you, you should be able to integrate it into your code as necessary.

Dim rng As Range
Set rng = Range("c5", "c6")
rng.Resize(5, 1).Select

Hope this helps. SuperBry!

 

[JohnCHolmes]

Moreover,
Selection.Resize(iNewSelRows, iNewSelCols).Select

Thank you. Star that gentleman please.

The only disturbing thought is how far this thread travelled to get there. Thanks Superbry!