Microsoft Excel - setting a variable range in vba

please can you help me to create a range, using vba - the top left hand corner of the range is fixed, but the number of columns and rows of the range change depending on the data. 

Specifically, I cant figure the coding to create a range which starts at cell c6, and is x colums wide and y rows high, where x is the number in cell a1, and y is the number contained in cell a2.

Any advice much appreciated!

I do this all the time using the Offset method of the range.  Here's some sample code:

Sub RangeTest()
    Dim rng As Range
    Dim rowOffset As Long
    Dim columnOffset As Long
    
    ' grab offets as width - 1 and height - 1
    rowOffset = Range("A2").Value - 1
    columnOffset = Range("A1").Value - 1
    
    ' set the "upper-left corner"
    Set rng = Range("C6")
    
    ' using offset and a reference to the range itself, "Stretch" the range
    Set rng = Range(rng, rng.Offset(rowOffset, columnOffset))
    
    MsgBox "The new Range is: " & rng.Address
    
    Set rng = Nothing
End Sub

Is this what you're looking for?

Good luck,

Brendon

Dynamic Ranges
--------------
It is often useful to create a name that refers to a range of cells, where the range depends on the content of the cells.  For example, you may want a name that refers to the first N non-blank entries in column A.   Excel's Name tool allows you to do this.  For example, creating a name called DynaRange, referring to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If the first 20 rows of column A contain data (and the rest are blank), DynaRange will refer to the range A1:A20

See the on-line help for the =OFFSET function for a description of the arguments.  Setting the Width argument to 2 will allow us to use this name in a =VLOOKUP function

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

Then, call VLOOKUP with the DynaRange argument for the lookup range:

=VLOOKUP(C1, DynaRange, 2)

As data is added to columns A and B, the range search by VLOOKUP will extend to include the new data. Refer http://www.cpearson.com/excel/named.htm for details.

Named Ranges:
-------------

To to create a macro to automatically create named ranges for each sheet within the workbook. I want the named range to equal the sheet name if possible. The actual range for each sheet will always remain the same.
Here's an example, naming the range A1:B2 on each sheet.

Sub nameRanges()
   Dim sht As Worksheet
   For Each sht In ThisWorkbook.Worksheets
       ActiveWorkbook.Names.Add Name:=sht.Name, _
           RefersTo:=sht.Range("a1:b2")
   Next sht
End Sub

Example in VB:

Private Sub LoadAndFormatData(ByVal dr As SqlDataReader)
    Dim i, j As Integer

    ' Copy the data in from the SqlDataReader.
    i = 1
    While dr.Read
        i = i + 1
        For j = 0 To 1
            xlSheet.Cells(i, j + 1) = dr(j).ToString()
        Next j
    End While
    dr.Close()

    ' Format the columns.
    DirectCast(xlSheet.Columns(1), _
        Excel.Range).AutoFit()

    With DirectCast(xlSheet.Columns(2), _
        Excel.Range)
        .NumberFormat = "0.00"
        .AutoFit()
    End With
End Sub

-Paresh