How to create a variable selected range

K

kittronald

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.
 
K

kittronald

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.
 
C

Claus Busch

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
 
K

kittronald

Jim,

Showoff !

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

Thanks again.



- Ronald K.
 
X

XLS S

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
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top