macros dont update

[itsme]

Is there a way to have macros update when you change the sheet. For example:
If you have a macro that does a sort and you later add rows the macro doesnt seem to know that rows were added?

Thanks

 

[Bob Phillips]

You need to manage it yourself, there is no way the code could know.

One way is to use a dynamic range, either coded dynamically, or with dynamic
named ranges.

Example of former

cLastRow = Cells(Rows.Cou8nt,"A").End(xlUP).Row

Set rng = RAnge("A1").Resize(cLastRow,12)

rng.Sort etc.

Example of letter

define a name like so

=OFFSET(Sheet2!$A$1,,,COUNT(Sheet2!$A:$A),12))
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a way to have macros update when you change the sheet. For example:
If you have a macro that does a sort and you later add rows the macro

doesnt seem to know that rows were added?

 

[Earl Kiosterud]

You,

The easiest way is to use a range in your sort macro that extends to the
bottom of the worksheet. Doesn't work so well if you have totals or other
stuff after your list.

In that case, you can use a named range, and your macros can "know" when a
range has been extended. In many cases, you can use the CurrentRegion in
the range to be sorted. Or you can define your range (Insert - Name -
Define), use that name in your Sort method in the macro, and leave an empty
row at the bottom, inserting new rows just before that one. The range
expands as you insert rows. Another is the Dynamic Range technique. It
depends on some column always having an entry in your rows. All this
depends on how your table has been set up.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Is there a way to have macros update when you change the sheet. For example:
If you have a macro that does a sort and you later add rows the macro

doesnt seem to know that rows were added?