Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Multiple Ranges with Certain Data to Print | Excel Programming | |||
select multiple ranges in formula | Excel Discussion (Misc queries) | |||
How to select multiple ranges in Excel with vbs | Excel Programming | |||
in charting, how do i select data ranges from multiple sheets, sa. | Charts and Charting in Excel | |||
select multiple cell ranges in "sumif" formula? | Excel Worksheet Functions |