CODEWITHSUNDEEP
c#excelexcel-interop
Carson
Carson

Reputation: 35

C# UsedRange count incorrect

I used UsedRange property of an Excel.Worksheet object to get row and column counts. If I try to delete column or row, the UsedRange count should reduce accordingly. However, after a lot of debugging, in certain order of changing the Hidden property and column width of the column. The count will not change accordingly, which should suggest an anchor is set for the cell. Please help explain (as it affects my codes a lot).

Test Codes:

Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
Debug.WriteLine("Used Range Column Count 1: " + worksheet.UsedRange.Columns.Count);

worksheet.Range["A1"].Formula = "1";
worksheet.Range["B1"].Formula = "1";
worksheet.Range["C1"].Formula = "1";
worksheet.Range["D1"].Formula = "1";
Debug.WriteLine("Used Range Column Count 2: " + worksheet.UsedRange.Columns.Count);

Range column = worksheet.Columns[4];
column.Delete();
Debug.WriteLine("Used Range Column Count 3: " + worksheet.UsedRange.Columns.Count);

worksheet.Range["D1"].Formula = "1";
column = worksheet.Columns[4];
column.ColumnWidth = 20.0;
column.Hidden = true;
column.Hidden = false;
column.Delete();
Debug.WriteLine("Used Range Column Count 4: " + worksheet.UsedRange.Columns.Count);

worksheet.Range["D1"].Formula = "1";
column = worksheet.Columns[4];
column.Hidden = true;
column.Hidden = false;
column.ColumnWidth = 20.0;
column.Delete();
Debug.WriteLine("Used Range Column Count 5: " + worksheet.UsedRange.Columns.Count);

Results:

Used Range Column Count 1: 1 (Expected)
Used Range Column Count 2: 4 (Expected)
Used Range Column Count 3: 3 (Expected)
Used Range Column Count 4: 4 (Not Expected)
Used Range Column Count 5: 3 (Expected)

Further descriptions on the necessity for the correct used range count:

I am working on an application which can enhance the excel interface. For example, the user have a list of inventories and purchasing cost with a sub total or average price line at the end. (the actual sub total line will be a lot more complicated than sum and average) Therefore, the list should be expandable but the sub total line cannot. As a result, I need to track if new row or new column is added or removed, so I need to cross check the count in the excel worksheet against the historical (cache) count. Since the new row or column may not have actual data on it, I used the UsedRange property to determine the current row/column count shown. Therefore, the fact that UsedRange includes row/column which are deleted make my calculation impossible.

Although I cannot find a way to lift the anchor effect, I finally figured out a way to do the job:

Using a single cell name range to mark the last cell of the worksheet, with UsedRange property to initialize the range.

If you need to avoid showing users about the range, you can try saving the data in customed properties in a worksheet. However, this approach will need a lot more considerations on the user actions tracking (named range reference will automatically change if you add row/column)

Upvotes: 2

Views: 2240

Answers (1)

Xaver
Xaver

Reputation: 1041

Well, UsedRange doesn't work well for determining the last row and column. Instead, you can use this code:

// get last row
var WorksheetLastRow = worksheet.Cells.Find(
    What: "*",
    SearchOrder: Excel.XlSearchOrder.xlByRows,
    SearchDirection: Excel.XlSearchDirection.xlPrevious,
    MatchCase: false
).Row;

// get last column
var WorksheetLastCol = worksheet.Cells.Find(
    What: "*",
    SearchOrder: Excel.XlSearchOrder.xlByColumns,
    SearchDirection: Excel.XlSearchDirection.xlPrevious,
    MatchCase: false
).Column;

Upvotes: 1

Related Questions