Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |