Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
I'm trying to create a macro that will do the following:
1) Goto B1 2) Using the variable number value stored in A1 (i.e., 10), select B1 and the next nine adjacent cells to the right - creating a range of 10 selected cells (B1:B10). The goal is to select an X number of cells from a known starting point. Any ideas ? - Ronald K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Range("B1").Resize(1, Range("A1").Value2).Select -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Rows add-in: Custom Shading, Deleting, Inserting) "kittronald" wrote in message ... I'm trying to create a macro that will do the following: 1) Goto B1 2) Using the variable number value stored in A1 (i.e., 10), select B1 and the next nine adjacent cells to the right - creating a range of 10 selected cells (B1:B10). The goal is to select an X number of cells from a known starting point. Any ideas ? - Ronald K. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Jim,
Thanks, that works ! Using that code, I'm trying to fill down to the last used row. For example: A B C D 1 ABC 1 2 3 2 BCD 3 CDE 4 DEF 5 EFG In this case, B1:D1 would be filled down to row 5. However, the rows used in column A varies and I'm trying to fill down to the last used row with code similar to: Range(Selection, Selection.SpecialCells(xlCellTypeLastCell)).Select Selection.FillDown Is there a way to specify the last used row ? - Ronald K. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Hi Ronald,
Am Sat, 20 Aug 2011 02:30:15 -0400 schrieb kittronald: A B C D 1 ABC 1 2 3 2 BCD 3 CDE 4 DEF 5 EFG In this case, B1:D1 would be filled down to row 5. 'Last used column LCol = Cells(1, Columns.Count).End(xlToLeft).Column 'Last used Row LRow = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(1, 2), Cells(1, LCol)).AutoFill _ Destination:=Range(Cells(1, 2), Cells(LRow, LCol)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
You might be interested in my recent universal last row function. It gets the last row from a worksheet, a range or a column and accommodates for Lists or Tables. free... http://blog.contextures.com/archives...ith-excel-vba/ -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Rows add-in: Custom Shading, Deleting, Inserting) "kittronald" wrote in message ... Jim, Thanks, that works ! Using that code, I'm trying to fill down to the last used row. For example: A B C D 1 ABC 1 2 3 2 BCD 3 CDE 4 DEF 5 EFG In this case, B1:D1 would be filled down to row 5. However, the rows used in column A varies and I'm trying to fill down to the last used row with code similar to: Range(Selection, Selection.SpecialCells(xlCellTypeLastCell)).Select Selection.FillDown Is there a way to specify the last used row ? - Ronald K. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Jim,
Showoff ! You know, you should do this Excel thing for a living. Thanks again. - Ronald K. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Claus,
Vielen Dank ! - Ronald K. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
Hey ,
try this code Sub Selectandpaste() Range("B1:D1").Select Selection.Copy Range("a1").Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Select Range(Selection, Range("B1")).Select Selection.PasteSpecial End Sub On Aug 20, 6:15*am, "kittronald" wrote: * * I'm trying to create a macro that will do the following: * * * * 1) Goto B1 * * * * 2) Using the variable number value stored in A1 (i.e., 10), select B1 and the next nine adjacent cells to the right - creating a range of 10 selected cells (B1:B10). * * The goal is to select an X number of cells from a known starting point. * * Any ideas ? - Ronald K. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
XLS S,
Thanks, I'll give it a try ! - Ronald K. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a variable selected range
XLS S brought next idea :
Hey , try this code Sub Selectandpaste() Range("B1:D1").Select Selection.Copy Range("a1").Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Select Range(Selection, Range("B1")).Select Selection.PasteSpecial End Sub Why so many lines of code?... Sub Selectandpaste() Range("B1:D1").Copy Range(Range("A1").End(xlDown).Offset(0, 1), Range("B1")).PasteSpecial Application.CutCopyMode = False End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba: attach a variable name to a selected range | Excel Programming | |||
Naming variable range selected by cursor movement | Excel Programming | |||
how to test whether selected variable is within defined range | Excel Programming | |||
how do i create a formula for selected range? | Excel Worksheet Functions | |||
How do you get a Selected Range address into a variable? | Excel Programming |