Range question

[strataguru]

Hi,

I'd like to select a Range in column 1 from Row 1 to 7. Another tim
it may be some other random column for a difference range of rows.

So I'm wondering - how can I numerically select a range. I can't us
hard coded values like:

Range("A1:A7").Select

How can I select my Range dynamically for a ad-hoc group of cells?

Thanks in advanc

 

[Guest]

This might be a little funkier than you wanted but here goes...

copy everything below here to a new workbook module

'This function will set a range
public function GetRange (byval strSheetName as string, byval lngFirstRow as
long, byval lngLastRow as long) as range

set GetRange = sheets(strsheetname).range(lngFirstRow & ":" & lngLastRow)

end function

'Now to use the function...
sub test
dim rngMyRange as range

set rngmyrange = getrange("Sheet1", 3, 10)

'Now you have a range object with all the associated properties and methods.

rngMyRange.Select

end sub

'Hope this helps...

 

[Tom Ogilvy]

Sub AAABBB()
Dim rng As Range
For i = 1 To 10
Set rng = Cells(Int(Rnd() * 15 + 1), _
Int(Rnd() * 10 + 1)).Resize( _
Int(Rnd() * 20 + 1), 1)
Debug.Print rng.Address
rng.Select
Next i
End Sub

produced:

$F$13:$F$29
$C$14:$C$33
$C$15:$C$28
$J$2:$J$12
$F$1:$F$14
$H$2:$H$4
$C$1:$C$6
$J$5:$J$12
$C$7:$C$26
$G$3:$G$6



as an example.