Way to make range the whole spreadsheet
 

I'm working on VS 2005 with Excel 2003. I need to set the range for the
entire sheet, but I do not know the number of rows in use for the
spreadsheet. I tried this:
oRng = oSheet.Range("A1").EntireColumn
TotRows = oRng.Count
TotRows ended up 65,5xx. Not useful. I really don't need to set a range, I
just need to know how many rows are being used. I need to programmatically
scan down the first column to see if a particular string is there and if it
is go process the next 5 rows or so. To do this, I need the total number of
rows in use. Shoulod I just give up and search all 65K rows?

Does anyone know how to do this?
--
Bettie

Way to make range the whole spreadsheet
 

UsedRows = oSheet.UsedRange.Rows.Count

will usually do it, although from time to time Excel forgets what the
used range really is. You force it with

oSheet.UsedRange
UsedRows = oSheet.UsedRange.Rows.Count

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 15 Sep 2009 15:15:01 -0700, BGCSOCAL
wrote:

I'm working on VS 2005 with Excel 2003. I need to set the range for the
entire sheet, but I do not know the number of rows in use for the
spreadsheet. I tried this:
oRng = oSheet.Range("A1").EntireColumn
TotRows = oRng.Count
TotRows ended up 65,5xx. Not useful. I really don't need to set a range, I
just need to know how many rows are being used. I need to programmatically
scan down the first column to see if a particular string is there and if it
is go process the next 5 rows or so. To do this, I need the total number of
rows in use. Shoulod I just give up and search all 65K rows?

Does anyone know how to do this?

Way to make range the whole spreadsheet
 

Mega Thanks! Can you recommend a good article about this Excel programming
Excel 2003 and 2007? The one I have may be from VS2003 or earlier!
--
Bettie


"Chip Pearson" wrote:



UsedRows = oSheet.UsedRange.Rows.Count

will usually do it, although from time to time Excel forgets what the
used range really is. You force it with

oSheet.UsedRange
UsedRows = oSheet.UsedRange.Rows.Count

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 15 Sep 2009 15:15:01 -0700, BGCSOCAL
wrote:

I'm working on VS 2005 with Excel 2003. I need to set the range for the
entire sheet, but I do not know the number of rows in use for the
spreadsheet. I tried this:
oRng = oSheet.Range("A1").EntireColumn
TotRows = oRng.Count
TotRows ended up 65,5xx. Not useful. I really don't need to set a range, I
just need to know how many rows are being used. I need to programmatically
scan down the first column to see if a particular string is there and if it
is go process the next 5 rows or so. To do this, I need the total number of
rows in use. Shoulod I just give up and search all 65K rows?

Does anyone know how to do this?

Way to make range the whole spreadsheet
 

Take a look here and see if it helps:

http://www.google.com/search?sourcei...tutorial+excel



"BGCSOCAL" wrote in message
...
Mega Thanks! Can you recommend a good article about this Excel programming
Excel 2003 and 2007? The one I have may be from VS2003 or earlier!
--
Bettie


"Chip Pearson" wrote:



UsedRows = oSheet.UsedRange.Rows.Count

will usually do it, although from time to time Excel forgets what the
used range really is. You force it with

oSheet.UsedRange
UsedRows = oSheet.UsedRange.Rows.Count

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 15 Sep 2009 15:15:01 -0700, BGCSOCAL
wrote:

I'm working on VS 2005 with Excel 2003. I need to set the range for the
entire sheet, but I do not know the number of rows in use for the
spreadsheet. I tried this:
oRng = oSheet.Range("A1").EntireColumn
TotRows = oRng.Count
TotRows ended up 65,5xx. Not useful. I really don't need to set a range,
I
just need to know how many rows are being used. I need to
programmatically
scan down the first column to see if a particular string is there and if
it
is go process the next 5 rows or so. To do this, I need the total number
of
rows in use. Shoulod I just give up and search all 65K rows?

Does anyone know how to do this?