Question about Range Name in Excel

[sjh]

Hi,

I defined a range called 'budgetCat' in an Excel worksheet. For now, it has 3 items in this range (A1:A3), but the user may add new categories.

When the user adds a new row (say A4), the range 'budgetCat' doesn't seem to include the new row. Is this something that I have to update programatically? If so, what is the code for updating the range name?

Thank you,

 

[DaleWatson123321]

Hi sjh,

Here's a routine for you...

Sub Set_BudgetCat()
Application.ScreenUpdating = False
[budgetCat].Activate
FirstCell = ActiveCell.Address
curcolm = ActiveCell.Column
LastCell = Cells(65536, curcolm).End(xlUp).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "budgetCat"
[A1].Select
Application.ScreenUpdating = True
End Sub

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[michaelbr55]

Hi,
If your users actually add another row, as opposed to just typing in the next emply row, the range will expand to include the new row. ie if they highlight row 3 and Insert>Row your budgetCat will now be A1:A4.

 

[xlbo]

instead of hardcoding a range reference, have a look at a dynamic named range:

Lets say your sheet is called Sheet1

Go Insert>Name>Define
delete your original range definition from the "budgetCat" name and enter this instead:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

The 1 at the end specifys that the width of the range be 1 column - you can expand this by entering a bigger number or by using
COUNTA(Sheet1!$1:$1)
which will expand the range to contain all rows AND all columns with information

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Loomah]

Hi
A word of warning regarding the dynamic range naming. I used it a while ago and it worked wonderfully apart from when I tried to use it in an array formula. The formula didn't seem to like it and gave some strange (read as wrong!) results.

Everything else worked fine and I never found a reason why the array fromula didn't like it when it worked ok with a fixed named range. So, there you go!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Loomah - there are no probs with using array formulae and dynamic range names - I have several reports that use the combination of the 2 - best guess is that you had missing entries in the data - therefore meaning that the one dyna range may be shorter than another - which will mess up array formulae.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244