Select multiple ranges of data
 

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

Select multiple ranges of data
 

Perhaps:

Intersect(ActiveSheet.UsedRange,Range("Z:Z,AP:AP") ).Select
--
Gary''s Student - gsnu2007j


" wrote:

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

Select multiple ranges of data
 

On Jul 31, 11:44*am, Gary''s Student
wrote:
Perhaps:

Intersect(ActiveSheet.UsedRange,Range("Z:Z,AP:AP") ).Select
--
Gary''s Student - gsnu2007j

" wrote:
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

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

Select multiple ranges of data
 

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


--
Gary''s Student - gsnu200797


" wrote:

On Jul 31, 11:44 am, Gary''s Student
wrote:
Perhaps:

Intersect(ActiveSheet.UsedRange,Range("Z:Z,AP:AP") ).Select
--
Gary''s Student - gsnu2007j

" wrote:
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

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

Select multiple ranges of data
 

On Jul 31, 12:16*pm, Gary''s Student
wrote:
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

--
Gary''s Student - gsnu200797

" wrote:
On Jul 31, 11:44 am, Gary''s Student
wrote:
Perhaps:

Intersect(ActiveSheet.UsedRange,Range("Z:Z,AP:AP") ).Select
--
Gary''s Student - gsnu2007j

" wrote:
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

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

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.