Variable Acting Like a Constant?

[George Boynton]

I have a problem with this statement: myrow =
Range("A1").CurrentRegion.Rows.Count + 1,
or as an alternative myrow = Range("A1").End(xlDown).Row + 1, when the
number of rows of data on a worksheet changes. My goal, using this
variable, is to determine the row number of the line just below the last
line of data.

Assume a worksheet with 10 rows of data with cell A1 included in the first
row of data. myrow = 11, and both of the two expressions just mentioned
will also evaluate to 11. That's great so far.

Assume later in the procedure some code adds 10 more rows of data starting
at row 11, increasing the total number of rows of data to 20. At this point
I have found that myrow still equals 11 while either of the two expressions
now evaluate to 21, the correct number. If subsequently, more rows of data
are added, the value of myrow remains constant at 11 while
Range("A1").CurrentRegion.Rows.Count or Range("A1").End(xlDown).Row + 1
will evaluate again to the correct number.

Why is the variable myrow acting like a constant? Note in my declarations I
did not precede myrow with "Const". How can I get myrow to act like a
variable?

Thanks.

 

[Bob Phillips]

You have to execute the code that sets myRow again whenever the data
changes, or more appropriately, just prior to the code that uses that
variable.

Variables are not event driven, that is they do not update when whatever
they refer to changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

You could be able to get away with something like that if you could code it
in a cell, name the cell and use that name as a variable or as
RANGE("name").Value in VBA. That way Excel would recalculate the cell's
formula and produce a new result whenever something changes.

=ROWS(rangename)

could be what you need IF in your code you make sure that "rangename"
expands whenever you add a line in your worksheet.