|
|
 | | From: | nimmi_srivastav at yahoo.com | | Subject: | Formula for last row and last column on a worksheet | | Date: | 17 Jan 2005 06:18:15 -0800 |
|
|
 | 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/microsoft.public.excel.programming/browse_frm/thread/feaee3a633a4676a/376fc5ef854b57fd?q=last+row&_done=%2Fgroup%2Fmicrosoft.public.excel.programming%2Fsearch%3Fgroup%3Dmicrosoft.public.excel.programming%26q%3Dlast+row%26qt_g%3D1%26&_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.
='\[]'!
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
|
|
| | From: | Tom Ogilvy | | Subject: | Re: Formula for last row and last column on a worksheet | | Date: | Mon, 17 Jan 2005 09:44:40 -0500 |
|
|
| >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
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
news:1105971495.307619.179530@c13g2000cwb.googlegroups.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/microsoft.public.excel.programming/browse_frm/thread/feaee3a633a4676a/376fc5ef854b57fd?q=last+row&_done=%2Fgroup%2Fmicrosoft.public.excel.programming%2Fsearch%3Fgroup%3Dmicrosoft.public.excel.programming%26q%3Dlast+row%26qt_g%3D1%26&_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.
> ='\[]'!
>
> 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
>
|
|
|
