Resize Range without Select

[achidsey]

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 Ramel]

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

 

[Rowan]

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]

Thomas and Rowan, Thanks very much - alan

 

[Patrick Molloy]

Sub SetRange()
Dim sRng As Range

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

Debug.Print sRng.Address

End Sub