473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 10691
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: RR | last post by:
Normally when I run a query, each record is displayed in a row, and the colums contain the field titles. Is there a way to switch this and have each record in a column, and the names of the...
7
by: deep022in | last post by:
guys, I have written a perl script using Win32::OLe module. I was able to read it completly. but I have to specify the number of rows in excel sheet as command line option. I want to know how...
0
by: sysmanint1 | last post by:
I am a total neophyte at Visual Basic but found the following post and reply from Clint concerning a dynamic range. Also, have never "posted" to a discussion I have made a macro that works on...
1
by: =?Utf-8?B?RGVicmEgTGFzc21hbg==?= | last post by:
I have installed Office 2007 on my machine and when I open Excel 2007 I still have 256 columns and 65,536 rows. I know in the 2007 version that you have the ability to have more than this. If I add...
0
by: Radu | last post by:
Hi. I'm trying to read the structure of some Excel 2000 file that the user is expected to upload. The file must be an excel file (solved) and it must contain a column named "PIN". That's all. Now...
0
by: JFKJr | last post by:
I have an excel file, which has columns C and D grouped together, I am trying to delete blank columns and rows from the excel file, ungroup the columns and import the file to MS Access using Access...
1
by: brionesl | last post by:
Hi, I have seen other threads with the same question but I'm not too familiar with Access and their solutions often include writing VB code or very complex SQL statements. What I want to do is...
5
by: jenniferhelen | last post by:
I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information...
0
by: ebernon | last post by:
The Dynamic deletion of Rows and Columns within a program is frequently desired but often hard to obtain. The Help files for VB-6 contained within Excel 2002 (XP) don’t always provide the help you...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.