Get Specific Column last Cell value from Excel using Office Interop library in Windows Application

Last Reply 4 months ago By bigbear

Posted 4 months ago

Hello everyone

i am trying to get the Last cell for a specific column so i can add a value to it.

I can get the last row used but i cant get it for a specified column.

Not all my columns are going to be the same last row value. so thats why i need it specified.

                //Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                //Excel.Range range = xlWorkSheet.get_Range("A1", last);
                //int lastRow = range.Row;

                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                Excel.XlDirection goDown = Excel.XlDirection.xlDown;

                var colLetter = GetLastCellForColumn("6");
                int testCount = GetRowsForColumn(6);

                /// Add New Values to their Specific Rows & Columns.
                //  Project IDrowCount + 1
                xlWorkSheet.Cells[testCount, 6] = project;
                //  Scale WeightrowCount + 1
                xlWorkSheet.Cells[testCount, 7] = data;
                //  Time StamprowCount + 1
                // xlWorkSheet.Cells[firstEmpty+1, 8] = DateTime.Now.ToString();
                //  Sequence IDrowCount + 1
                // xlWorkSheet.Cells[firstEmpty+1, 9] = sequence;
                /// Save the Newly Added Row.
                xlWorkBook.Save();

        private int GetRowsForColumn(int columnNumber)
        {
            int columnCount = 0;
            try
            {
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");

                // columnCount = xlWorkSheet.Columns[columnNumber].Rows.Count;
                foreach (Range c in xlWorkSheet.Columns[columnNumber].Rows)
                {

                    if (xlWorkSheet.Columns[columnNumber].Rows.Cells.Value != "")
                    {
                        columnCount++;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

            return columnCount;
        }

        private string GetLastCellForColumn(string columnNumber)
        {
            string[] Columns = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S" };
            int transferNum = Convert.ToInt32(columnNumber);

            return Columns[transferNum - 1];
        }
Posted 4 months ago

here is my solution

        private int GetRowsForColumn(int columnNumber)
        {
            int columnCount = 0;
            try
            {
                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");

                columnCount = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, columnNumber].End(goUp).Row;
            }
            catch(Exception ex) { MessageBox.Show(ex.Message); }

            return columnCount;
        }