Sub SetRange()
Dim sRng As Range

Set sRng = Range(Range("A2"), Range("B2").End(xlDown).Offset(0, -1))

Debug.Print sRng.Address

End Sub

"achidsey" wrote:

Excel Experts,

I have a spreadsheet similar to the following:

A B
1 Side Trades
2 DELL
3 INTC
4 AMD

I want to create a Range Variable, SRng, below the heading "Side", that has
as many rows as there are filled cells below the heading "Trades".

The below code works, but it forces me to Select the range after I resize
it. If I take the Select out, the code fails.

Sub ResizeTest()

Dim TRng As Range
Dim SRange As Range

Set THeading = Cells.Find(What:="Trades")
Set TRng = Range(THeading.Offset(1), THeading.End(xlDown))

Set SHeading = Cells.Find(What:="Side")
SHeading.Select
Set SRng = SHeading.Offset(1)

SRng.Select
SRng.Resize(TRng.Rows.Count).Select
Set SRng = Selection

End Sub

How can I resize SRange without the Select?

Thanks,
Alan
--
achidsey