How to get the coordinate of the lower-bound cell in Excel 2003 with C#

[zlf]

Hi all,
I am using C# to extract data from Excel 2003. I want to know how to get
the column number and row number of cells with real data in the current
sheet.
Thanks
E.g
A B C D E F G H
1 1 1 1
2 1 1 1
3
4
5

Column Number:3
Row Number:2

zlf

 

[Norman Jones]

Hi Zlf,

I am using C# to extract data from Excel 2003. I want to know how to
get the column number and row number of cells with real data in the
current sheet

There is no built in function which will reliably return the last data cell.

The following udf will return this cell:

'==========================>>
Function LastDataCell(Sh As Worksheet) As Range
On Error Resume Next
Set LastDataCell = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
On Error GoTo 0
End Function
'<<==========================

The following Sub demonstrates usage:

'==========================>>
Sub Tester()
Dim rng As Range
Dim sAdd As String
Dim Rw As Long
Dim Col As Long

Set rng = LastDataCell(ActiveSheet)

Rw = rng.Row
Col = rng.Column
sAdd = rng.Address(0, 0)

MsgBox "Address: " & sAdd & vbNewLine & _
"Row : " & Rw & vbNewLine & _
"Column: " & Col
End Sub
'<<==========================

 

[Harlan Grove]

....

OP is using C#, so VBA code may not help much.

For the OP: each worksheet object has a UsedRange property, which is a range
object containing at the cells containing data *or* nondefault formatting.
Range objects, such as UsedRange, have Column and Row properties which are
integers giving the column and row indices of the top-left cell in the
range, respectively. Ranges also have Columns and Rows properties which are
in turn collection objects that are the separate columns and rows of the
range, respectively. They have count properties.

So the bottommost row index for the UsedRange in the ws worksheet is

ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1

and the rightmost column in it is

ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

 

[Norman Jones]

Hi Harlan,

OP is using C#, so VBA code may not help much.

True.

For the OP: each worksheet object has a UsedRange property, which is a
range object containing at the cells containing data *or* nondefault
formatting. Range objects, such as UsedRange, have Column and Row
properties which are integers giving the column and row indices of the
top-left cell in the range, respectively. Ranges also have Columns and
Rows properties which are in turn collection objects that are the separate
columns and rows of the range, respectively. They have count properties.

So the bottommost row index for the UsedRange in the ws worksheet is

ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1

and the rightmost column in it is

ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

This does not assist the OP in his quest to determine the last cell "with
real data", as explicitly stipulated.

 

[Harlan Grove]

This does not assist the OP in his quest to determine the last cell "with
real data", as explicitly stipulated.

OK, but sometimes other languages only have access to properties, not
methods. If that's the case, then iteration within UsedRange using the max
row and column bounds could be one approach.

All the others involve trapping errors. One alternative involves using
UsedRange.SpecialCells in 2 calls to find cells containing formulas and
cells containing constants. Take the max of the row and columns indices of
the last cells in each (e.g., with cf containing the cells containing
formulas as returned by SpecialCells,

cf.Cells(cf.Cells.Count).Row

and similarly for columns).

As for your approach, cells containing just a prefix character, ', could be
considered cells containing data. Your Find method call won't find them.

Also, if data looked like

___A__B__C
1__x_____y
2_________
3__z______

your approach would return cell A3, but the column index wouldn't reflect
that there's anything to the right of column A. You'd need a second Find
call going columnwise to ensure you also find the rightmost column.

Returning the 'last cell' needs to be robustly handled. No single property
or method call does that.

 

[Tom Ogilvy]

All the others involve trapping errors.

Even though Norman's method has error handling, it can be avoided by testing
for nothing.

 

[Harlan Grove]

Tom Ogilvy wrote...

Even though Norman's method has error handling, it can be avoided by testing
for nothing.

....

OK, fair point. However, two calls are still needed to find the
rightmost column containing data and the bottommost row containing
data, since those separate values may come from different cells.