Creating a Range (VBA/Excel)

[Barguast]

Can someone tell me how I can initialise a Range so it defines a (rectangular) region on the spreadsheet? I want to be able to define it in terms of the top-left corner and bottom-right corner or, failing that, the top-left corner and the width and height of the range.

I've been trying to use the Offset and Resize methods of the Range class, but whilst the Offset method works, the Resize method doesn't seem to exist outside of the few examples I've found on the internet.

Thanks in advance,
Barguast

[DKenny]

What is the intent here? Any range of cells will be a rectangle

[Barguast]

What is the intent here? Any range of cells will be a rectangle

Yes I know, but I just want to know how to create a Range object from non-constant cell coordinates. For example how would I create a range object which began in cells 'x', 'y' and was 'w' in width and 'h' in height?

The only way I know of creating a Range object is with an Excel-style name such as "A1:B5".

[DKenny]

Try this.

The idea being to keep adding cells to the right of the range until I get to the desired width, then adding cells to the bottom of the range until I get to the desired height.

Sub BarguastRange()
Dim RWidth As Long
Dim RHeight As Long
Dim Result As Range
 
    RWidth = 1000
    RHeight = 250
    
    Set Result = Range("A1")
    
    Do While Result.Width < RWidth
        Set Result = Application.Union(Result, Result.Offset(0, 1))
    Loop
    Do While Result.Height < RHeight
        Set Result = Application.Union(Result, Result.Offset(1, 0))
    Loop
    
    Result.Select
    
End Sub

[si_the_geek]

You can use R1C1 notation to specify a range, eg:

"R1C1:R5C2" (="A1:B5")

[Barguast]

You can use R1C1 notation to specify a range, eg:

"R1C1:R5C2" (="A1:B5")

That might be the best method. Something along the lines of...

Private Function GetRange (x1 as Integer, y1 as Integer, x2 as Integer, y2 as Integer) as Range

    Dim rangestr as String
    range = "R" + CStr (y1) & "C" & CStr (x1) & ":R" & CStr (y2) & "C" * + Cstr (x2)

    GetRange = Sheet.Range (rangestr)

End Function

... presumably? Still, I'd have thought there was a better way. Did the Range class even have a Resize method? As I mentioned in my first post, I've seen it used in example on the internet, but the method doesn't seem to exist when I try to use it.

[DKenny]

I thought you wanted a specifc width and height, but what you want is a specific number of cells, right?
If so then the following would work.

Function GetRange(x1 As Integer, y1 As Integer, x2 As Integer, y2 As Integer) As Range
    GetRange = Range(Cells(x1, y1), Cells(x1, y1).Offset(x2, y2))
End Function

[Barguast]

I thought you wanted a specifc width and height, but what you want is a specific number of cells, right?
If so then the following would work.

Function GetRange(x1 As Integer, y1 As Integer, x2 As Integer, y2 As Integer) As Range
    GetRange = Range(Cells(x1, y1), Cells(x1, y1).Offset(x2, y2))
End Function

That is perfect.

Many thanks to you both.

[Barguast]

Urgh, sorry to double post, but this ought to be a simple enough question.

How can I (programatically) set the viewable range of the Worksheet? There is a 'Viewable Range' field in the 'Commands and Options' form in Design View which works perfectly fine, but I want to be able to alter the viewable range whilst the program is running.