Range used in a macro needs to be variable
 

I want to highlight a range by starting in the top left hand corner(fixed),
doing an 'end' 'down' and then right 6, because the number of rows is
variable each time, can this be down in a macro. It seems the macro records
the actual cell references it found whilst recording the macro and therefore
only works the first time.

I read the article under Making Excel Macros where you can use the Variables
DIM and SET to name the range, but it relies on the user highlighting the
selection before running the macro.

I am using Excel 2000 and 2003.

Thanks

Range used in a macro needs to be variable
 

Dave,

Dim rng As Range
Set rng = Range(Selection, Selection.End(xlDown)).Resize(, 6).Select

works assuming there is a selected range to start with.

If you want to use a static start point, then replace both Selection by say
Range("H1").

The 6 can be replaced by a variable with a number in it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DaveP" wrote in message
...
I want to highlight a range by starting in the top left hand
corner(fixed),
doing an 'end' 'down' and then right 6, because the number of rows is
variable each time, can this be down in a macro. It seems the macro
records
the actual cell references it found whilst recording the macro and
therefore
only works the first time.

I read the article under Making Excel Macros where you can use the
Variables
DIM and SET to name the range, but it relies on the user highlighting the
selection before running the macro.

I am using Excel 2000 and 2003.

Thanks

Range used in a macro needs to be variable
 

Hi Bob

Sounds like just the thing, but I must be doing something wrong now. I am
trying to use your second suggestion of replacing the 2 Selection by
Range("A3") like below, and then tries to sort the range 'myrange'

Dim myRange As Range
Set myRange = Range(Range("A3"), Range("A3").End(xlDown)).Resize(,
6).Select
myRange.Sort Key1:=Range("A4"), Order1:=xlAscending,
Key2:=Range("D4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

but get Run-Time Error '424' Object Required.


Dave


"Bob Phillips" wrote:

Dave,

Dim rng As Range
Set rng = Range(Selection, Selection.End(xlDown)).Resize(, 6).Select

works assuming there is a selected range to start with.

If you want to use a static start point, then replace both Selection by say
Range("H1").

The 6 can be replaced by a variable with a number in it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DaveP" wrote in message
...
I want to highlight a range by starting in the top left hand
corner(fixed),
doing an 'end' 'down' and then right 6, because the number of rows is
variable each time, can this be down in a macro. It seems the macro
records
the actual cell references it found whilst recording the macro and
therefore
only works the first time.

I read the article under Making Excel Macros where you can use the
Variables
DIM and SET to name the range, but it relies on the user highlighting the
selection before running the macro.

I am using Excel 2000 and 2003.

Thanks

Range used in a macro needs to be variable
 

Sorry Dave, I made an error in changing my test code to that which I posted.
You don't set and Select.

This should work for you

Dim myRange As Range
Set myRange = Range(Range("A3"), Range("A3").End(xlDown)) _
.Resize(, 6)
myRange.Sort Key1:=Range("A4"), _
Order1:=xlAscending, _
Key2:=Range("D4"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DaveP" wrote in message
...
Hi Bob

Sounds like just the thing, but I must be doing something wrong now. I am
trying to use your second suggestion of replacing the 2 Selection by
Range("A3") like below, and then tries to sort the range 'myrange'

Dim myRange As Range
Set myRange = Range(Range("A3"), Range("A3").End(xlDown)).Resize(,
6).Select
myRange.Sort Key1:=Range("A4"), Order1:=xlAscending,
Key2:=Range("D4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom

but get Run-Time Error '424' Object Required.


Dave


"Bob Phillips" wrote:

Dave,

Dim rng As Range
Set rng = Range(Selection, Selection.End(xlDown)).Resize(, 6).Select

works assuming there is a selected range to start with.

If you want to use a static start point, then replace both Selection by
say
Range("H1").

The 6 can be replaced by a variable with a number in it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DaveP" wrote in message
...
I want to highlight a range by starting in the top left hand
corner(fixed),
doing an 'end' 'down' and then right 6, because the number of rows is
variable each time, can this be down in a macro. It seems the macro
records
the actual cell references it found whilst recording the macro and
therefore
only works the first time.

I read the article under Making Excel Macros where you can use the
Variables
DIM and SET to name the range, but it relies on the user highlighting
the
selection before running the macro.

I am using Excel 2000 and 2003.

Thanks