Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,comp.sources.d
|
|||
|
|||
Formula for last row and last column on a worksheet
I have been trying the formula for last row and last column on a
worksheet as provided in a previous posting: http://groups-beta.google.com/group/...c5ef 854b57fd The formulas a =Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row =Sheets("Sheet2").Range("IV1").End(xlToLeft).Offse t(0, 1).Column The former is for finding the last row of a worksheet and the latter is for finding the last column of a worksheet. Unfortunately, I am getting an error with these formulas. Any help will be appreciated. Also, I had a need for formulas that referenced data in another spread-sheet in the Excel-specified format i.e. ='<directory-name\[<file-name]<sheet-name'!<cell-name For example, ='C:\My Documents\Spread-sheets\[Info04-01-09.xls]MainSheet'!F15 Here is what I did. I first stored all the excel files in the directory (.xls extension) in a file. Then I created a UNIX shell-script that read the file names and converted them into the formulas. This was certainly a lot of pain, but it worked like a charm. I want to now know how I could achieve this using Excel macros. Thanks, Nimmi |
#2
Posted to microsoft.public.excel.programming,comp.sources.d
|
|||
|
|||
Formula for last row and last column on a worksheet
The formulas a
=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row =Sheets("Sheet2").Range("IV1").End(xlToLeft).Offs et(0, 1).Column Those are not formulas, they are VBA statements. Check out the worksheet.functions news group if you want formulas. -- Regards, Tom Ogilvy wrote in message oups.com... I have been trying the formula for last row and last column on a worksheet as provided in a previous posting: http://groups-beta.google.com/group/...c5ef 854b57fd The formulas a =Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row =Sheets("Sheet2").Range("IV1").End(xlToLeft).Offse t(0, 1).Column The former is for finding the last row of a worksheet and the latter is for finding the last column of a worksheet. Unfortunately, I am getting an error with these formulas. Any help will be appreciated. Also, I had a need for formulas that referenced data in another spread-sheet in the Excel-specified format i.e. ='<directory-name\[<file-name]<sheet-name'!<cell-name For example, ='C:\My Documents\Spread-sheets\[Info04-01-09.xls]MainSheet'!F15 Here is what I did. I first stored all the excel files in the directory (.xls extension) in a file. Then I created a UNIX shell-script that read the file names and converted them into the formulas. This was certainly a lot of pain, but it worked like a charm. I want to now know how I could achieve this using Excel macros. Thanks, Nimmi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy a formula within a row from a column on another worksheet | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
FIND A TEXT IN A WORKSHEET NOT IN A COLUMN OR ROW BY FORMULA | Excel Discussion (Misc queries) | |||
Formula that uses Last Number in Column of another worksheet | Excel Discussion (Misc queries) | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions |