resize a range, based on combobox value

[twinpowr]

Hi guys, i have been trying to resize a named range based on a combobox value

i thought something like this perhaps. but this is not working.

Range(combobox2.value).Resize(, -1).Select

The combobox is part of a userform that moves advisors from eg team1 to team3, so team1 would be resize -1 as team1 is losing a member and team 3 would be +1 as they are gaining a member. Moving the advisors is working great, but i then need to resize the ranges so that they are then reflected in the combobox drop down for the next time a move is required.

the ranges are all named with managers and the advisors underneath the mangers, there are 23 named ranges, so the named range will change depending on which advisor is being moved.

is this even possible. any help would be apprecieated

Ally.

 

[GlennUK]

Have you read the help for Resize?

 

[twinpowr]

Hi Glenn

yes. have read the excel help pages, (im using excel 2003). resizing a named range is not a problem as long as the named range remains static. The problem i have is the named ranges are not the same each time the userform is run.

one occasion there could be an advisor moving from team 1 to team 4.

the next time it could be team 5 to team 10 and so on.

I will continue to check the forum and excel help pages.

its starting to drive me a little bit nuts.

thanks for any help.

Ally

 

[GlennUK]

Your arguments for the Resize are blank and -1, which Excel takes as keep the number of rows the same and make the number of columns be -1 .... ( that's not reducing the number of columns by 1, it's asking to have a range area of width of -1 columns ... i.e. impossible ).

 

[twinpowr]

Thanks Glenn

That actually makes sense. which for this point on a friday is great for me

Ally

 

[twinpowr]

Ok so i solved this problem, I thought i would post my solution.

Whenever an advisor is moved from one list i use this code.

Advisor = ActiveCell.Value
        Selection.Delete Shift:=xlUp

Excel keeps track of this automatically and reassigns the correct number of cells.

when the array called advisor adds the advisors name to the new managers list i use this code

ActiveCell.Offset(2, 0).Select
Selection.Insert Shift:=xlDown
ActiveCell = Advisor

again as i am entering data within the named range parameters, excel keeps the correct number of cells.

I think, I was thinking about it too much, when in fact the solution was much simpler.

If anyone needs anymore of this code to help them, then please let me know.

Thanks

Ally.