Re: [ExcelVBA] define a range then use it in code

 

There are lots of things wrong with this ...

> Dim TRANGE As Range
> TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown).Row

.Row returns a row number, not a range. Therefore it will fail when you try
to assign it to your range variable. But you don't want a row number
anyway.

> TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown)

This will also fail. You need the SET keyword for a range assignment. So

> Set TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown)

will put a range reference into TRANGE.

> Range("TRANGE").Select

is trying to select the range with the name TRANGE. I assume you don't have
one, and this will fail. What you actually want to do is to select the
range whose reference you have just put into TRANGE. So

> TRANGE.Select

Now, there's an added complication ...

> TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown).Row
> TRANGE.Select

will not do what you probably expect it to.

Firstly, it will only look in column A, so it'll drop down to the bottom of
the worksheet unless you have something in column A.

Also, it'll stop at the bottom value in column A, regardless of whether
there are lower values in the other columns.

And also, it'll only select the cell in column A, not all of the A:U column
range. So if you want the selection to cover the multiple columns, you need
to extend it. There are two ways to do this: either by extending when you
select, or by extending when you create TRANGE.

I.e.

Dim TRANGE As Range
Set TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown)
TRANGE.Resize(1, 21).Select

will select all the columns, but still leave TRANGE as a single cell. Or

Dim TRANGE As Range
Set TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown).Resize(1, 21)
TRANGE.Select

will put all 21 cells in the TRANGE reference.

Regards, Dave S

----- Original Message -----
From: "Manny" mgarza@fellowes.com>
To: ExcelVBA@yahoogroups.com>
Sent: Thursday, March 07, 2013 1:38 AM
Subject: [ExcelVBA] define a range then use it in code

>I am using the following code to define and declare a range:
>
> Dim TRANGE As Range
> TRANGE = Worksheets("Sheet1").Range("A1:U1").End(xlDown).Row
>
> I tried to have the range selected using this code:
> Range("TRANGE").Select
>
> Of course that fails. What is the proper code to have this range selected?
> Or does this make sense at all? I am a novice to VBA.
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1430 / Virus Database: 2641/5651 - Release Date: 03/05/13
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 8

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___