VB.NET - Defining a variable Range using VB - Asked By ben0926 humble on 13-Nov-08 05:17 AM

I am hoping someone might have some suggestions, I am trying to use a some code to define a range and name it "Region". The range always starts in the same cell, but depending on the data it will end in a different place. This is what I've come up with, which is not working, any suggestions?

Thanks.

Function Test()
Dim x As Range

Sheets("raw data").Range("b3").Select
x = Range(ActiveCell, ActiveCell.End(xlDown)).Select

ActiveWorkbook.names.Add Name:="Region", RefersToR1C1:="x"

End Function

Does this function help?

Sub MakeRange(start As String, numCells As Integer, rangeName As String)
    Range(start).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, numCells)).Select
    ActiveWorkbook.Names.Add name:=rangeName, RefersTo:=Selection
End Sub

You can call it as follows:

Call MakeRange("B4", 5, "newRangeA")

B4 is the starting cell.

5 is the number of cells (same row) to include in the range.

"newRangeA" is the name of the range.

You can extend this to define both a row and column offset from the starting cell if that's needed.

HTH

-Rd

Thanks Richard that works well...

I've simplified it a bit after seeing how you wrote your bit and this seems to work.

Much appreciated.

Function Test()
Dim x As Range

Sheets("mastersheet").Range("b3").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

ActiveWorkbook.Names.Add Name:="Region", RefersToR1C1:=Selection

End Function

Richard.