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.

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" <(E-Mail Removed)>
wrote in message
news:j2n1se$ii7$(E-Mail Removed)...
> 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.

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, B11 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.

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, B11 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

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" <(E-Mail Removed)>
wrote in message
news:j2nka2$ts1$(E-Mail Removed)...
> 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, B11 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.
>

Jim,

Showoff !

You know, you should do this Excel thing for a living.

Thanks again.



- Ronald K.

Claus,

Vielen Dank !



- Ronald K.

Hey ,

try this code

Sub Selectandpaste()
Range("B11").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" <kittron...@yahoo.com> 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.

XLS S,

Thanks, I'll give it a try !



- Ronald K.

XLS S brought next idea :
> Hey ,
>
> try this code
>
> Sub Selectandpaste()
> Range("B11").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("B11").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