Range used in a macro needs to be variable

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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)