last row function
 

Hi,
I'm currently using excel to log production data throughout the day
using vb scripting, to enter the data on the right row i use
"lastrow=excelapp.selection.specialcells(11).r ow"
I would like to use another sheet to analyse this data. This was okay
the first time I tried it. The problem is at the end of the day the
production data gets backedup and the current day file has all the
production data deleted "excelapp.selection.delete." I have to use the
delete command as it is the only way I can seem to get the lastrow to
go back to the top of the sheet.
The problem with using the delete is the new sheet I created that
references the data ends up with #REF in all the cells that referenced
the sheet that was deleted. :o(

Does anyone know of a way I can get around this.

Any help would be appreciated

Rich

last row function
 

Hi Rich,

You might want to consider defining the last row like:

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

Change "A" to the column of interest.

To reset tthe used range, see Debra Dalgleish's notes at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman



"rich" wrote in message
oups.com...
Hi,
I'm currently using excel to log production data throughout the day
using vb scripting, to enter the data on the right row i use
"lastrow=excelapp.selection.specialcells(11).r ow"
I would like to use another sheet to analyse this data. This was okay
the first time I tried it. The problem is at the end of the day the
production data gets backedup and the current day file has all the
production data deleted "excelapp.selection.delete." I have to use the
delete command as it is the only way I can seem to get the lastrow to
go back to the top of the sheet.
The problem with using the delete is the new sheet I created that
references the data ends up with #REF in all the cells that referenced
the sheet that was deleted. :o(

Does anyone know of a way I can get around this.

Any help would be appreciated

Rich

last row function
 

if column A will always have data use

lastrow = excelapp.activesheet.cells(rows.count,1).End(xlup) .row

if your data is organized like a database

Dim rng as Excel.Range
set rng = excelapp.activesheet.Range("A1").currentregion
lastrow = rng.rows(rng.rows.count).row

--
Regards,
Tom Ogilvy


"rich" wrote in message
oups.com...
Hi,
I'm currently using excel to log production data throughout the day
using vb scripting, to enter the data on the right row i use
"lastrow=excelapp.selection.specialcells(11).r ow"
I would like to use another sheet to analyse this data. This was okay
the first time I tried it. The problem is at the end of the day the
production data gets backedup and the current day file has all the
production data deleted "excelapp.selection.delete." I have to use the
delete command as it is the only way I can seem to get the lastrow to
go back to the top of the sheet.
The problem with using the delete is the new sheet I created that
references the data ends up with #REF in all the cells that referenced
the sheet that was deleted. :o(

Does anyone know of a way I can get around this.

Any help would be appreciated

Rich

last row function
 

Hi Rich,


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

was intended as:

Dim LRow as long


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


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Rich,

You might want to consider defining the last row like:

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

Change "A" to the column of interest.

To reset tthe used range, see Debra Dalgleish's notes at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman