Select multiple ranges of data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sbitaxi@gmail.com - 31 Jul 2008 16:09 GMT

Hello:

I'm trying to tell Excel to select multiple columns, but a want only
the cells in the data range selected. If I use the following it
selects the entire column -

Range("Z:Z,AP:AP").Select

which is close but I want it to select only as far as the last row of
data.

What I am trying to get is -

Last = LRow(WS)
Range("Z3:Z" & Last,"AP3:AP" & Last).Select

But it selects everything as if I had entered
Range("Z3:AP"&Last).Select

Any help is greatly appreciated. I guess I don't quite understand how
the Range function works.

Steven

Reply to this Message

Gary''s Student - 31 Jul 2008 16:44 GMT

Perhaps:

Intersect(ActiveSheet.UsedRange,Range("Z:Z,AP:AP")).Select

Signature

Gary''s Student - gsnu2007j

> Hello:
>
[quoted text clipped - 19 lines]
>
> Steven

Reply to this Message

sbitaxi@gmail.com - 31 Jul 2008 16:55 GMT

On Jul 31, 11:44 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Perhaps:
>
[quoted text clipped - 26 lines]
>
> > Steven

Hmmmm, it does select the range limits quite nicely, but I am now
getting a 424 error, Object required. This is the code that I am
applying it to -

For Each MyCell In Intersect(ActiveSheet.UsedRange,
Range("Z:Z,AP:AP")).Select
MyCell.Formula = MyCell.Value & " " _
& MyCell.Offset(0, 1).Value & " " _
& MyCell.Offset(0, 2).Value & " " _
& MyCell.Offset(0, 3).Value & " " _
& MyCell.Offset(0, 4).Value
MyCell.Formula = LTrim(MyCell.Formula)
MyCell.Formula = RTrim(MyCell.Formula)
Next

Reply to this Message

Gary''s Student - 31 Jul 2008 17:16 GMT

Couple of comments:

1. You don't need the Select in the For statement:
For Each MyCell In Intersect(ActiveSheet.UsedRange, Range("Z:Z,AP:AP"))

2. Make sure you have Dim'ed MyCell as a Range

3. Make sure the equals sign is in the formula:
MyCell.Formula="1+2" will not work as a formula
MyCell.Formula="=1+2" will work as a formula

Signature

Gary''s Student - gsnu200797

> On Jul 31, 11:44 am, Gary''s Student
> <GarysStud...@discussions.microsoft.com> wrote:
[quoted text clipped - 43 lines]
> MyCell.Formula = RTrim(MyCell.Formula)
> Next

Reply to this Message

sbitaxi@gmail.com - 31 Jul 2008 17:25 GMT

On Jul 31, 12:16 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Couple of comments:
>
[quoted text clipped - 58 lines]
> > MyCell.Formula = RTrim(MyCell.Formula)
> > Next

Thanks Gary, I think it was probably the select, the formulas worked
fine but I was concerned about the macro running over more rows than
necessary. The worksheet I am working on has 656 rows of data but was
running on all 65000+ with the entire column selected.

This is great.

Reply to this Message