Formula for last row and last column on a worksheet

  • Thread starter Thread starter nimmi_srivastav
  • Start date Start date
N

nimmi_srivastav

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..._doneTitle=Back+to+Search&&d#376fc5ef854b57fd


The formulas are:
=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

=Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(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
 
The formulas are:
=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

=Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Column
Click to expand...

Those are not formulas, they are VBA statements.

Check out the worksheet.functions news group if you want formulas.

--
Regards,
Tom Ogilvy


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..._doneTitle=Back+to+Search&&d#376fc5ef854b57fd


The formulas are:
=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

=Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(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
Click to expand...
 
Back
Top