Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find the last used row in Excel with C# [duplicate]

Possible Duplicate:
How to get the range of occupied cells in excel sheet

Im trying to find the last used row in an Excel worksheet. For doing this, I'm using this code:

int lastUsedRow = currentWS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;

Most of the time it works fine, but sometimes Excel thinks that there's more rows in the sheet than theres suppose to be.

Fx: If I copy data from sheet1, containing 12 rows, to an empty sheet2, then deleting all data in sheet2, by right clicking and press "Delete..." and copy data from sheet3, containing 5 rows, to sheet2, then lastUsedRow will give me the value of 12 which should have been 5.

enter image description here The image example above is suppose to give my the value of 22 as row count, but instead i'll get 634. Notice the scroll bar to the right.

It seems like Excel thinks that some cells are filled even though I just deleted all cells, before copying new data with fewer rows into the sheet.

Are there any way to "resize" the view of the data in the sheet, so that i'll get the right number of used cells or maybe another way to find the number of the last used row?

Thanks.

like image 606
Harving Avatar asked Sep 05 '12 07:09

Harving


People also ask

How do I find the last data row in Excel?

To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.

How do you reference the last row of a table in Excel?

To get the last row of your table, use Range("A1"). End(xlDown). Row , provided your "A" column does not have any blank cells.

What key is used at last row?

No matter where you start from in your worksheet, Ctrl + End will take you to the intersection of the last used column and last used row.


3 Answers

Edit: New Solution

Since Joe provided the correct code to get the last used row.

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

And using the following command to clear content and formattings

Selection.Delete
Selection.ClearFormats

This should work ;)

like image 188
lorenz albert Avatar answered Nov 18 '22 07:11

lorenz albert


To get the last used row in a worksheet, you can use:

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

Note that:

  • UsedRange does not necessarily start on the first row - the first row might be empty.

  • UsedRange includes cells that contain formatting even if their content is empty. I suspect this is why you're seeing values bigger than you expect. You need to delete formatting as well as data from the empty cells.

I'm not sure how to do the deletion of formatting on cells programmatically

You can use Range.ClearFormats to clear formatting, or Range.Clear to clear everything. In general, if you're not sure how to do something programatically in Excel, try recording a macro, doing it manually, then inspecting the generated macro.

like image 33
Joe Avatar answered Nov 18 '22 06:11

Joe


Here is the code I use:

public static string GetMinimalUsedRangeAddress(Excel.Worksheet sheet)
{
    string address = String.Empty;
    try
    {
        int rowMax = 0;
        int colMax = 0;

        Excel.Range usedRange = sheet.UsedRange;
        Excel.Range lastCell = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
        int lastRow = lastCell.Row;
        int lastCol = lastCell.Column;
        int rowMin = lastRow + 1;
        int colMin = lastCol + 1;

        int rr = usedRange.Rows.Count;
        int cc = usedRange.Columns.Count;
        for (int r = 1; r <= rr; r++)
        {
            for (int c = 1; c <= cc; c++)
            {
                Excel.Range cell = usedRange.Cells[r, c] as Excel.Range;
                if (cell != null && cell.Value != null && !String.IsNullOrEmpty(cell.Value.ToString()))
                {
                    if (cell.Row > rowMax)
                        rowMax = cell.Row;
                    if (cell.Column > colMax)
                        colMax = cell.Column;
                    if (cell.Row < rowMin)
                        rowMin = cell.Row;
                    if (cell.Column < colMin)
                        colMin = cell.Column;
                }
                MRCO(cell);
            }
        }

        if (!(rowMax == 0 || colMax == 0 || rowMin == lastRow + 1 || colMin == lastCol + 1))
            address = Cells2Address(rowMin, colMin, rowMax, colMax);

        MRCO(lastCell);
        MRCO(usedRange);
    }
    catch (Exception ex)
    {
        // log as needed
    }
    return address; // caller should test return for String.Empty
}


public static string Cells2Address(int row1, int col1, int row2, int col2)
{
    return ColNum2Letter(col1) + row1.ToString() + ":" + ColNum2Letter(col2) + row2.ToString();
}


public static string ColNum2Letter(int colNum)
{
    if (colNum <= 26) 
        return ((char)(colNum + 64)).ToString();

    colNum--; //decrement to put value on zero based index
    return ColNum2Letter(colNum / 26) + ColNum2Letter((colNum % 26) + 1);
}


public static void MRCO(object obj)
{
    if (obj == null) { return; }
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch
    {
        // ignore, cf: http://support.microsoft.com/default.aspx/kb/317109
    }
    finally
    {
        obj = null;
    }
}

Note: you might be tempted to replace all the individual cell value checks with CountA but that will fail in certain cases. For example, if a cell contains the formula =IF(A1=55,"Y",""), a resulting empty string will count as a non-blank cell using CountA.

like image 29
CtrlDot Avatar answered Nov 18 '22 07:11

CtrlDot