2

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)

Carson
  • 35
  • 8
  • I converted your code to VBA and ran it. It produced the same results. I think this has something to do with the mysterious way the `UsedRange` object works. I've read several posts in the past few days, and I'm still trying to figure out how it works. – vknowles Jan 14 '17 at 19:59
  • Yes, very frustrated. I can understand they need to have a certain way to set an anchor to determine which is the last used cell, but they should provide a way to remove the anchor. – Carson Jan 15 '17 at 04:39
  • I don't think they do it on purpose, looks more like another Excel bug, the trigger being hiding a range whose width was changed. Not even [saving the file](http://stackoverflow.com/a/11454176/11683) helps it. – GSerg Jan 15 '17 at 07:29

1 Answers1

1

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;
Xaver
  • 1,035
  • 9
  • 17
  • I used UsedRange extensively in my codes...I am okay with using UsedRange to get any row count as long as it can be deleted... So, no ideas regarding this? – Carson Jan 15 '17 at 04:43
  • I just tried this method but it cannot capture formatted cells, any more ideas? – Carson Jan 15 '17 at 16:47
  • The problem with false results for UsedRange doesn't just occur with VSTO, it also occurs when using Excel directly: Imagine you have a worksheet with e.g. 8 rows and 2 columns. By pressing Ctrl+End on the keyboard, the last cell (i.e. the cell that is in the last row and in the last column) should get selected. That works, you get the cell in row 8, column 2. But if you delete one of the 8 rows, you would imagine to get row 7, column 2 when pressing Ctrl+End. But this doesn't work, you still get row 8 column 2. – Xaver Jan 15 '17 at 22:23
  • Concerning your problem with formatted cells: Can you describe your problem in more detail? I would be glad to help, but I'm not sure what the exact problem is. – Xaver Jan 15 '17 at 22:25
  • The issue you provided is the hindrance on my application. I figured out a way (updated in the question) to temporarily get the result I need but it comes with some issues. Please advice if you have more ideas. – Carson Jan 17 '17 at 15:46