469,086 Members | 1,146 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Columns and Rows in Excel

Greetings,

How can I find the number of active columns and rows used in an excel work
sheet?
What I mean is how can i find the last column and row or cell position,
where the data is stored in an excel sheet?

A code snippet would be of great help.

Thanks for your cooperation and help.

Best regards,
Anand

Dec 19 '05 #1
5 10476
Anand wrote:
Greetings,

How can I find the number of active columns and rows used in an excel work
sheet?
What I mean is how can i find the last column and row or cell position,
where the data is stored in an excel sheet?


Is the worksheet currently open in Excel or OpenOffice,
and you wish to query the application for that information?

Or do you want to read the spreadsheet in from a file?

--
Steven.

Dec 19 '05 #2
In Python you probaly best follow a recipe like this.

1 Save the file in Excel as a csv file.
2.Study the reader object of Python's csv module :
http://docs.python.org/lib/module-csv.html
3. Read a row for row in in a list, split it on comma, count the
elements, the maximum of all these is the number of columns you look
for.
4. The number of rows you read is the number of rows in the sheet.

Dec 19 '05 #3
Greetings,

The worksheet is currently opened in Excel.
And I want to read the data from the excel worksheet.
Instead of looping through the entire worksheet, I want to limit the looping
to the rows and columns used so far!

Thanks and regards,
Anand

"Steven D'Aprano" <st***@REMOVEMEcyber.com.au> wrote in message
news:43************@REMOVEMEcyber.com.au...
Anand wrote:
Greetings,

How can I find the number of active columns and rows used in an excel work sheet?
What I mean is how can i find the last column and row or cell position,
where the data is stored in an excel sheet?


Is the worksheet currently open in Excel or OpenOffice,
and you wish to query the application for that information?

Or do you want to read the spreadsheet in from a file?

--
Steven.

Dec 19 '05 #4
> "Steven D'Aprano" <st***@REMOVEMEcyber.com.au> wrote in message
news:43************@REMOVEMEcyber.com.au...
Anand wrote:

Greetings,

How can I find the number of active columns and rows used in an excel
work
sheet?
What I mean is how can i find the last column and row or cell position,
where the data is stored in an excel sheet?
Is the worksheet currently open in Excel or OpenOffice,
and you wish to query the application for that information?

Or do you want to read the spreadsheet in from a file?

Anand wrote [at the top of the post, which was naughty]: Greetings,

The worksheet is currently opened in Excel.
And I want to read the data from the excel worksheet.
Instead of looping through the entire worksheet, I want to limit the looping to the rows and columns used so far!


An observation: if I record a macro and then use CTRL/End to go to the
"bottom right-hand corner" of the spread, I see that Excel records:

ActiveCell.SpecialCells(xlLastCell).Select

It's then just a matter of finding out the row and column number of the
currently-selected cell (or of xlLastCell if you don't want to change
locations).

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

Dec 19 '05 #5
Anand wrote:
Greetings,

How can I find the number of active columns and rows used in an excel work
sheet?
What I mean is how can i find the last column and row or cell position,
where the data is stored in an excel sheet?

A code snippet would be of great help.

Thanks for your cooperation and help.

Best regards,
Anand


Anand,

A worksheet has a UsedRange property that may be what you need.

Gerard

using Excel = Microsoft.Office.Interop.Excel;

private Excel.Workbook thisWorkbook = null;

protected void ThisWorkbook_Open()
{
Excel.Worksheet sheet;
Excel.Range usedrange;
Excel.Range lastcell;

sheet = (Excel.Worksheet) thisWorkbook.Sheets[1];
usedrange = sheet.UsedRange;
lastcell = sheet.Cells[ usedrange.Rows.Count, usedrange.Columns.Count
];
}
If you have access to the Spreadsheet in question you can name ranges
of cells:

Insert -> Names -> Define

Then

usedrange = sheet.get_Range( "DATA", Type.Missing );

for example. (Don't worry about Type.Missing).

Dec 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by RR | last post: by
1 post views Thread by =?Utf-8?B?RGVicmEgTGFzc21hbg==?= | last post: by
reply views Thread by Radu | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.