Auto update of range

[mpezeur]

Using Excel2k. Have several ranges defined through Excel (not VB). When I add/remove a row to/from a range, the range does not automatically update to the new beginning/end values. For example, say range A is set to $C$1 to $F$4. I delete row 4, and then range then sets to C1:!REF.

I'm trying to institude a command button that will add a line below the current row, maintaining the values from the first column, and would like that new row to be added/remain part of the the original row's range.

How does Excel handle ranges when a row is added - does it automatically make sure it is part of the previous range?

 

[SkipVought]

Hi,
To do this well and without fail, you must use VBA. I have written faq68-1331 that has some sample code and instructions.

I almost always use named ranges. This procedure automatically chages the address definition of a named range as you add or delete rows from a table.

You must adhere to the following rules about tables
1. One row of headings
2. No empty rows or columns within table

The procedures use a range property called CurrentRegion. You can get the CUrrent Region Icon for your toolbar in the Edit Category of Tools/Customize

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Depending on your situation, there are a couple of things you could try:

If you aren't using database functions, pivot tables, and such, you could include an extra blank row from the bottom of the data in the range. Then when you select that row and insert, the range will adjust automatically. However, some operations may not behave the way you want when there is a blank row in the range.

If you can't tolerate a blank row in the range, it's a bit of a bother, but you could select the last row of the range, insert a row, then copy the last row back up a row into the newly inserted space. Messy, but it does auto-adjust the range.

Again, depending on your situation, VBA may be the best way to go.

 

[plantj]

Try setting the range to something like

=OFFSET($A$1,0,0,COUNTA($A:$A),8)

where
A1 is the top left cell
A is a column that is never blank for a row in the range
8 is the number of columns in the range

This method has some limitations but it works pretty well for most situations I've encountered.

 

[mpezeur]

thanks, guys. I'll try these suggestions today.