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

Grüezi achidsey

achidsey schrieb am 13.09.2005


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

How can I resize SRange without the Select?

You could use somethng like this:

Public Sub SetRange()
Dim lngRows As Long
Dim SRng As Range

lngRows = Application.WorksheetFunction.CountA _
(Cells.Find(What:="Trades").EntireColumn) - 1
Set SRng = Cells.Find(What:="Side").Offset(1, 0).Resize(lngRows)

SRng.Select 'just for testing
End Sub



Regards
Thomas Ramel

--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)

Another way:

Dim THeading As Range
Dim SHeading As Range
Dim SRng As Range
Dim eRow As Long

Set THeading = Cells.Find(What:="Trades")
eRow = Cells(Rows.Count, THeading.Column).End(xlUp).Row

Set SHeading = Cells.Find(What:="Side")
Set SRng = Range(Cells(SHeading.Row + 1, SHeading.Column), _
Cells(eRow, SHeading.Column))
Debug.Print SRng.Address


Regards
Rowan

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

Thomas and Rowan, Thanks very much - alan
--
achidsey

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