Resizing a Selected Range using VBA

[Jeffrey W Smith]

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:

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

 

[SlinkRN]

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.

Sub test()

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

End Sub

HTH, Slink

 

[Jeffrey W Smith]

Slink,

Sorry, that didn't work.

Jeff

 

[agihcam]

how about;

Range(ActiveCell, ActiveCell.Offset(10000, 0)).Select
Application.SendKeys "^+{UP}"

 

[SlinkRN]

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

 

[SlinkRN]

Oh, you want the whole range not just the last cell. agihcam's code works great for that

 

[Jeffrey W Smith]

That is definately what I was looking for. Thanks Agihcam!