Excel Visual Basic - help please

  howryou 15:57 24 Dec 06
Locked

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

New Corel ParticleShop plugin now available: 11 new brushes & 6 new brush packs

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…