Excel Visual Basic - help please

  howryou 15:57 24 Dec 2006

I want to specify a name of a range in a spreadsheet except that every time that the macro is run the range can vary si I will have to delete the named area when initialising the macro.

Normally it would be something along the lines of
ActiveWorkbook.Names.Add Name:="COMMTABLE",_ RefersToR1C1:= "=Sheet4!R1C1:R4C2"

Except that instead of the relative positions I want to specify the range by including a variable - something along the lines of
"sheet4!Cells(1, 1),Cells(2,Size)"

What is the correct way of doing this

  postie24 16:05 24 Dec 2006

Im not too sure,but VoG™ will come to the rescue for you

  VoG II 16:11 24 Dec 2006

Sub test()
Dim r As Range, size As Integer
size = 5
With Sheets("Sheet4")
Set r = Range(.Cells(1, 1), .Cells(2, size))
End With
MsgBox r.Address(False, False)
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires