find the last used row in a column in windows c#

Question

I have a excel report and i need to draw charts based on the data in the report. Am able to get the range from a particular column to last filled row like shown below. I have many columns in my report and i need only the data in a particular column like ("c1"," c12"). the column length may vary. it need not be 12. How can i get the range till last filled row of a column.

Excel.Range last1 = xlWorkSheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
oRange = xlWorkSheet2.get_Range("A6", last1);

Which VS version are you working with? My answer will depend on that. — Siddharth Rout, Nov 07 '13 at 10:24
In fact see [THIS](http://stackoverflow.com/questions/19353980/inserting-multiple-textbox-data-into-an-excel-file/19355932#19355932) I have already covered it there. Esp, see the `MORE FOLLOWUP (From Comments)` section in that answer. — Siddharth Rout, Nov 07 '13 at 10:26
its vs2010 . from the link i found this int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1 ; But how can i use it in the range. oRange = xlWorkSheet2.get_Range("A6", _lastRow );--getting error — Robin, Nov 07 '13 at 11:48
with .find am getting the no of rows filled in that column. How do i specify that in a range. i need range ("A6", "B --till last filled") — Robin, Nov 07 '13 at 12:10
do you still want an answer to your above comment since you have already accepted an answer? — Siddharth Rout, Nov 07 '13 at 15:43

Brett Wolfington · Accepted Answer · 2013-11-07T21:22:41.113

1

Try the following code. This works by selecting the top cell in a row, and then searching downwards until the end of the range is found. The range column is simply the range between start and end. Note that this will only find the last contiguous cell in the range, and will not search through blank rows.

Excel.Range start = xlWorkSheet2.Range["A1"];
Excel.Range column;
if (start.Offset[1].Value != null)
    column = xlWorkSheet2.Range[start, start.End[Excel.XlDirection.xlDown]];
else
    column = start;

The following code will allow you to retrieve the full used range of the column even if there are blank rows. This code works in a similar manner, but searches upwards from the bottom of the used range in the worksheet to find the last cell in the column containing a value.

Excel.Range start = xlWorkSheet2.Range["A1"];
Excel.Range bottom = xlWorkSheet2.Range["A" + (ws.UsedRange.Rows.Count + 1)];
Excel.Range end = bottom.End[Excel.XlDirection.xlUp];
Excel.Range column = xlWorkSheet2.Range[start, end];

edited Nov 07 '13 at 21:22

answered Nov 07 '13 at 12:22

Brett Wolfington

6,587
4
32
51

`xlDown` or `UsedRange` is the wrong way to find the last row which contains data. – Siddharth Rout Nov 07 '13 at 15:41
Care to explain why you feel that that is the case? I tested both of these methods, and both of them work. What is your alternative solution? – Brett Wolfington Nov 07 '13 at 17:50
2

Sure. :) Let's take the case of `xlDown` first. Lets say you have data from Cell `A1` to `A1000`. Now delete any value say in cell `A300` Now try xldown. What would you get. The best way is to use `xlup` as shown in [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) link. This link will also address the `usedrange` issue – Siddharth Rout Nov 07 '13 at 17:53
The first code snippet that I provided will only find the last row in the contiguous range, as I explained in my solution. The second code snippet will find the last row in the column that contains data. I attempted your example, and the second snippet worked perfectly. I also attempted to format a cell below the last cell containing data, as mentioned in the link you provided, and that worked perfectly as well. Can you please provide an example in which the second snippet will fail to work as intended? – Brett Wolfington Nov 07 '13 at 18:00
you mean if data is till cell A1000 and say A1500 is colored red then `usedrange` gives you A1:A1000? – Siddharth Rout Nov 07 '13 at 18:18
Another scenario where `xldown` will give you unexpected results. Let's say there is data only in cell A1 and there is no other data in col A. Then `xldown` will still give you unexpected results. It will not give you `1` as the last row. And hence to escape from all these possibilities, one should always use `xlup` like you did. – Siddharth Rout Nov 07 '13 at 18:25
`int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row` will give you last row and then you can use `_lastRow` in place of `(ws.UsedRange.Rows.Count + 1)` in `Excel.Range bottom = xlWorkSheet2.Range["A" + _lastRow];` And this was my point :) – Siddharth Rout Nov 07 '13 at 18:29
In response to your comment about coloring a cell red, you are correct. If I have data in cells A1:A1000, and A1500 is colored red, then the second example I gave, using UsedRange, gives me A1:A1000. I've also tried adding a value to A1500 and deleting it. UsedRange still gives A1000. Is it possible that this was the case in a previous version of Excel but has been changed? I'm using Excel 2010. – Brett Wolfington Nov 07 '13 at 21:14
Kind of hard for me to believe :) I am also using excel 2010. You might wanna see this http://wikisend.com/download/967686/1.png – Siddharth Rout Nov 07 '13 at 21:20
Good point about the first code snippet malfunctioning if there is only data in A1. I updated the code to fix that issue. – Brett Wolfington Nov 07 '13 at 21:22
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40775/discussion-between-brett-wolfington-and-siddharth-rout) – Brett Wolfington Nov 07 '13 at 21:25

Kaptein Babbalas · Answer 2 · 2016-06-03T07:48:43.203

Hi found that all the above methods didn't work for what I wanted to do, so here is my solution:

public object GetLastNotEmptyRowOfColumn(string sheet, string column,int startRow,int endRow)
{

    try
    {
       var validColumn =  Regex.IsMatch(column, @"^[a-zA-Z]+$");
        if(!validColumn)
        {
            throw new Exception($"column can only a letter. value entered : {column}");
        }
        xlBook = xlApp.ActiveWorkbook;
        xlSheet = xlBook.Sheets[sheet];
        xlRange = xlSheet.Range[$"{column}{startRow}", $"{column}{endRow}"];
        object[,] returnVal = xlRange.Value;
        var rows = returnVal.GetLength(0);
       // var cols = returnVal.GetLength(1);

        int count = 1;
        for (int r = 1; r <= rows; r++)
        {
            var row = returnVal[r, 1];
            if (row == null) break;
            count++;                  
        }

       //returns an object : {Count:10,Cell:A9}
        return= new { Count=count-1, Cell=$"{column}{startRow+count-1}" };

    }
    catch (Exception ex)
    {
      ......
    }
    return null;
}

Usage: var response = GetLastNotEmptyRowOfColumn("Sheet1", "A",1,100);

Result:

find the last used row in a column in windows c#

2 Answers2