Select multiple ranges of data

[sbitaxi]

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

 

[Gary''s Student]

Perhaps:

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

 

[sbitaxi]

Perhaps:

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

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

 

[Gary''s Student]

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

 

[sbitaxi]

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

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.