Resizing a Selected Range using VBA

Jeffrey W Smith

New Member
Joined
Nov 7, 2004
Messages
27
I've been working with VBA for awhile, but I've always had a problem changing the size of a selected range.

For instance, if I have a column of numbers starting in Cell C3, but don't know how many rows it uses, then I have to create a range that goes to the last used cell. Just to make it interesting, there may be blank cells within the column.

With the activecell on the first value in the column:

Code:
Range(activecell,activecell.offset(10000,0)).select
Range(Selection,selection.end(xlup)).select

The last statement doesn't work because it resizes the top of the selected range to Cell C1.

Any suggestions?

Jeff
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
Hi Jeff,
I'm not sure if this is what you want, but try this and see if it works for you. This gave the last result in that column for me.
Code:
Sub test()

ActiveCell.Offset(10000, 0).Select
Selection.End(xlUp).Select

End Sub

HTH, Slink
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
how about;
Code:
Range(ActiveCell, ActiveCell.Offset(10000, 0)).Select
Application.SendKeys "^+{UP}"
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701

ADVERTISEMENT

That's strange, it works for me. What result do you get when you run it?
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
Oh, you want the whole range not just the last cell. agihcam's code works great for that :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,406
Messages
5,547,759
Members
410,811
Latest member
adustin42
Top