Getting maximum row/column number from Excel Range

Question

I have a certain Range, for example:

Range rng = activeWorksheet.Range["A1", "B50"];

I've been retrieving the maximum row and column number used in this Range by looping through it and assigning a row number to my variable, but there must be a better way? I've been doing this:

int maxRow = 0;
foreach (Range row in rng.Rows)
     maxRow = row.Row;

Answer 1

I had this on a previous project:

wb = excel.Workbooks.Open(fileName);
worksheet = wb.ActiveSheet;            
Range usedRange = worksheet.UsedRange;

this.lastRow = usedRange.Rows.Count;
this.lastCell = usedRange.Columns.Count;

Using lastRow will give you the emptylines which in my case we not useful.

Answer 2

If you don't mind evaluating the string Address... by using ".EntireRow.Address(False, False)" we get a string that's easily parsed...

C#...

int maxRow = range.EntireRow.Address(false, false).Split(",:".ToCharArray()).Select(n => int.Parse(n)).Max();
string maxCol = range.EntireColumn.Address(false, false).Split(",:".ToCharArray()).Select(s => s.PadLeft(3)).Max().Trim();

VB... ;

Dim maxRow As Integer = -1
Dim maxCol As String = ""
range = ws.Range("$BB9:$C11")
maxRow = range.EntireRow.Address(False, False).Split(",:".ToCharArray()).Select(Function(n) Integer.Parse(n)).Max() ' 9:11
maxCol = range.EntireColumn.Address(False, False).Split(",:".ToCharArray()).Select(Function(s) s.PadLeft(3)).Max().Trim() ' C:BB

given range BB9:C11

maxRow is 11 from 9:11 ...if not parsed to int, then the Max() would be 9 due to string sorting

maxCol is "BB" from C:BB ...if not padded left, then the Max() would be "C" due to string sorting

range = ws.Range("A1:A3,AE15:AE9,C4:C7")
maxRow = range.EntireRow.Address(False, False).Split(",:".ToCharArray()).Select(Function(n) Integer.Parse(n)).Max() ' 1:3,9:15,4:7
maxCol = range.EntireColumn.Address(False, False).Split(",:".ToCharArray()).Select(Function(s) s.PadLeft(3)).Max().Trim() ' 1:3,9:15,4:7

and given range non-contiguous range: A1:A3,AE15:AE9,C4:C7

maxRow is 15 from 1:3,9:15,4:7 ...even with non-contiguous range, and referenced out of order= AE15:AE9

maxCol is "AE" from C:AE ...even with non-contiguous range

this DOES work with whole COLUMN selection "A:A"

this does NOT work with whole row selection "3:3"

Answer 3

I think I found the most elegant solution for this, something like this:

int maxRow = rng.Row + rng.Rows.Count - 1;
int maxColumn = rng.Column + rng.Columns.Count - 1;

rng.Row will retrieve the first used row number in the range, rng.Rows.Count - 1 will retrieve the total amount of rows used in this range and we also deduct 1 to get the correct maximum row number.

Answer 4

How about you search for the last used Row?

public int FindLastFilledRowInRange(Range range)
{
  var cell = range.Find("*", SearchOrder: Excel.XlSearchOrder.xlByRows, SearchDirection: Excel.XlSearchDirection.xlPrevious);
  return cell.Row;
}

If you start later than row 1 you can just do some additional math on the returned row...