Dynamically Update Defined Names Range

[Rem0ram]

Hi ExcelGeeks

Can some one help me in changing the named ranges dynamically.

Attached the sample file with requirement.

Best
Rem0

[CodeNinja]

RemOram,
You can use the following function to re-name a range. Your post was not clear about the conditions that would cause you to activate this function, so I will leave that to you, or you can explain further those conditions.

For example, if you want this done when the user adds a new row, you could call this from the worksheet change event etc...

Oh, the function will return true if it worked, false if there was a problem...

[VBA]
Function reName(ByRef sDefinedName As String, ByRef rng As Range, ByRef sht As Worksheet) As Boolean

On Error GoTo errHandler:
ActiveWorkbook.Names(sDefinedName).RefersTo = sht.Name & "!" & rng.Address

reName = True
Exit Function
errHandler:
reName = False

End Function
[/VBA]

[Jan Karel Pieterse]

if the sheetname contains a space you're required to add ' around it...

[Rem0ram]

Hi CodeNinja

ManyThanks for looking in to this and sorry about the vague information.

I can have a command button to facilitate this code. Can you update the code to the sample file as i have only limited knowledge about vba.

Hi Jan Karel

Thanks for the info.

Best
Rem0

[CodeNinja]

RemOram,
A command button would work, but how would you determine exactly what range you want to use in the named ranges? Would there only be named ranges for "BS", "CFG", "MSB", "ULT", and "WM" or should this dynamically look for anything past RMSG/RMS/ and name a range for that?

By the way, good point Jan Karl, you are 100% correct, and I will add that to the function. Thanks.

CodeNinja.

[Rem0ram]

Hi CodeNinja

ManyThanks!

I'm thinking of a way read the string in each cell, for example for RMSG/RMS/BS group first 11 characters common.

Is there a way to use the string?

Best
Rem0

[snb]

You'd better dive into the fundamentals of VBA first.

[CodeNinja]

I am really trying to learn the fundamentals of VBA snb

[snb]

@CodeNinja

Unless I use @name, I am addressing the OP in his/her thread.
Although diving into fundamentals isn't a bad thing to do for anybody, I wasn't addressing you, codeninja.

[Kenneth Hobs]

Rem, for code to dynamical set the range to a named range, there must be logic. You must tell us the rules since it is not obvious from your example.

You would be better off breaking those into columns and use a dynamic range formula method for each column. Then, no VBA would be needed.

[CodeNinja]

@CodeNinja

Unless I use @name, I am addressing the OP in his/her thread.
Although diving into fundamentals isn't a bad thing to do for anybody, I wasn't addressing you, codeninja.

Ya, I knew... I was being silly snb... My very weird sense of humor.

[snb]

@CN

sorry for misinterpreting you......

[Rem0ram]

Hi Ken

Thanks a lot for the tip.

I'm really not that familiar with Offset formula however managed to obtain the result.

Thanks a Trillion....

Best
Rem0