Excel Visual Basic - help please

  howryou 15:57 24 Dec 06

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 06

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

  VoG II 16:11 24 Dec 06

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.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5