Range always being the right size
 

Hi all, this is bugging me!, I want to set a range in a formula that is
always only the cells with stuff in, I was going to put make named
ranges with a button but it doesnt want to play! any ideas?

Private Sub CommandButton2_Click()
Set Rng1a = Range("A2")
Set RnG1 = Range("A1").End(xlDown)
ActiveWorkbook.Names.Add Name:="ARNG", RefersTo:=Rng1a: Rng1
End Sub

The last line will not create the A range (ARNG), i just want a range
called ARNG that refers to a2 to the last cell with stuff in in column
A.

Im sure this should be easy!

(my formula in excel will then refer to ARNG)

Duncan

Range always being the right size
 

Private Sub CommandButton2_Click()
Range(Range("A2"), Range("A2").End(xlDown)).Name = "ARNG"
End Sub

HTH
--
AP

"Duncan" a écrit dans le message de
news: ...
Hi all, this is bugging me!, I want to set a range in a formula that is
always only the cells with stuff in, I was going to put make named
ranges with a button but it doesnt want to play! any ideas?

Private Sub CommandButton2_Click()
Set Rng1a = Range("A2")
Set RnG1 = Range("A1").End(xlDown)
ActiveWorkbook.Names.Add Name:="ARNG", RefersTo:=Rng1a: Rng1
End Sub

The last line will not create the A range (ARNG), i just want a range
called ARNG that refers to a2 to the last cell with stuff in in column
A.

Im sure this should be easy!

(my formula in excel will then refer to ARNG)

Duncan

Range always being the right size
 

Very simply done AP, im well impressed!,

I have just finally managed to work it by:

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Set EndA = Selection
ActiveWorkbook.Names.Add Name:="DRNG", RefersTo:=EndA


But I will change to your code now I can see how it should be done,
many thanks indeed!

Duncan