I'm quite new to Excel programming. I have a worksheet with change event
code calling a sub that builds a color and inventory matrix to the right of
a part number in column 1. The data is coming from XML files using MSXMLDOM
in code. One entry in column 1 will fill several columns and rows which is
all working. I now want to enhance this to be able to paste in several part
numbers into column 1 at once. How do I keep track of the cells with the
part numbers? The change event has a range parameter but as I insert rows
with my code to build the matrix between the numbers pasted in the range
keeps changing.

thanks
LJB

You can get the last row easily with this code

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

and use this in your tests.

--

HTH

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

"ljb" <. wrote in message ...
I'm quite new to Excel programming. I have a worksheet with change event
code calling a sub that builds a color and inventory matrix to the right
of
a part number in column 1. The data is coming from XML files using
MSXMLDOM
in code. One entry in column 1 will fill several columns and rows which is
all working. I now want to enhance this to be able to paste in several
part
numbers into column 1 at once. How do I keep track of the cells with the
part numbers? The change event has a range parameter but as I insert rows
with my code to build the matrix between the numbers pasted in the range
keeps changing.

thanks
LJB

Hi LJB,

Bob's code will work well to find the last row in the worksheet. Thanks
Bob!

Since your new to Excel automation programming I thought it appropriate to
point out a couple of common potential traps that may make your experience
go smoother.

You didn't say if you were using VB or VBA but it looks like VB. Be careful
with unqualified expressions. Make sure all of your method and property
references are qualified with an object. For example,

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

In this example Cells is qualified. It is referencing an uncoded WorkSheet
object. This can work in VBA, but can fail consistently or randomly in
compiled VB. The correct reference would be

Dim oApp as new Excel.Application

cLastRow = oApp.ActiveSheet.Cells ...

Also, many of my support cases involve problems with Excel not shutting
down properly. You can ensure Excel shuts down properly by always calling
the Quit method using late binding. Don't rely on simply setting the app
variable to nothing. Here's an example:

Dim oXLApp as new Excel.Application
Dim oObj as Object

' Do your automation

' Shut down Excel
set oObj = oXLApp
oObj.Quit
set oObj = nothing
set OXLApp = nothing

Hope this information is helpful.

Thanks,

Jim

Jim Vita
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.