I need to set a constant (say Truck, dimensioned as an integer) to the value
of the row number of a cell identified to the macro by a defined name (say
Fleet). How do I do that please?

You won't be able to set a constant based on the rownumber of a named
range as this may change - which means it can't be constant.

You could set it as a variable:

Dim Truck As Integer
Truck = Range("Fleet").Row

Hope this helps
Rowan

gvm wrote:
I need to set a constant (say Truck, dimensioned as an integer) to the value
of the row number of a cell identified to the macro by a defined name (say
Fleet). How do I do that please?

Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

Did you mean something like this:

Dim row1 As Long
Dim col1 As Integer
row1 = Range("Fleet").Cells(1, 1).Row
col1 = Range("Fleet").Cells(1, 2).Column
'etc

Regards
Rowan

gvm wrote:
Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

Dim r1 as long
dim c1 as long
dim r2 as long
dim c2 as long
dim myRng as range

with worksheets("sheet1")
set myrng = .range(.cells(r1,c1),.cells(r2,c2))
end with

(I'd use Long instead of integer.)


gvm wrote:

Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

--

Dave Peterson

Thanks Rowan and Dave,
Dave's response is close to what need (sorry Rowan I was not clearer in my
question) but I'm not quite there yet. I used Dave's information in the
following way:

Range(.Cells(row1, col1), .Cells(row2, col1 +6)).Select
Selection.ClearContents

and I get a compile error. The term ".cells" is highlighted and the error
message is "invalid or unqualified reference"

Thanks again in anticipation .. Greg

I had

with worksheets("sheet1")
set myrng = .range(.cells(r1,c1),.cells(r2,c2))
end with

That with/end with is important. It means the things that are prefixed with
dots belong to the previous With statement--in my example, Sheet1.

So you could use:

Range(Cells(row1, col1), Cells(row2, col1 +6)).ClearContents

or include that worksheet to make sure you get the correct sheet.

with worksheets("sheet1")
.Range(.Cells(row1, col1), .Cells(row2, col1 +6)).ClearContents
end with

(No need to select in either case.)

gvm wrote:

Thanks Rowan and Dave,
Dave's response is close to what need (sorry Rowan I was not clearer in my
question) but I'm not quite there yet. I used Dave's information in the
following way:

Range(.Cells(row1, col1), .Cells(row2, col1 +6)).Select
Selection.ClearContents

and I get a compile error. The term ".cells" is highlighted and the error
message is "invalid or unqualified reference"

Thanks again in anticipation .. Greg

--

Dave Peterson