Using VBA to change a range

[Roy Miller]

Hi
Can anyone tell me how to use VBA to change the size of a range. fo
instance, I have a list called 'People' and I would like to change i
range using VBA coding.

Any ideas

Thanks in advance

Henr

 

[Don Guillett]

See ans in programming. No need to multi post

 

[Hank Scorpio]

Can anyone tell me how to use VBA to change the size of a range. for
instance, I have a list called 'People' and I would like to change it
range using VBA coding.

It's always useful to say exactly what you have in mind with these
exercises, since there are usually multiple approcahes that you can
take.

For example, let's say that the range expands and contracts as new
names are added or old ones deleted. Here's one way to redefine the
range based on its Current Region (the area that you see selected when
you press [Ctrl] + [*]):

Sub ChangePeopleRange()

Application.Goto "People"

Selection.CurrentRegion.Select

'Redefine the range using the current region.
'You can use any method to get an address to
'redefine the range.
'Change Sheet1 to the name of the sheet.

ActiveWorkbook.Names.Add Name:="People", _
RefersToR1C1:="=Sheet1!" _
& Selection.Address(ReferenceStyle:=xlR1C1)

End Sub

You'd probably take a different approach if the range was a single row
or column within the Current Region, though the above approach could
be modified to work even then.

 

[Roy Miller]

Many thanks, that is just what I was looking for (I hope). Will try i
out

Henr