Excel VSTO: Selecting range according to properties
I am creating an Excel VSTO Add-In in C# with Visual Studio, and I'm using the library Microsoft.Office.Microsoft.Office.Interop.Excel .
This app gets a range (e.g. 4000 contiguous rows) which may contain thousands of hidden rows (by an usual Excel filter). So, I start using: // Gets the selected range Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range activeRange = Globals.ThisAddIn.Application.Selection.Cells; Excel.Range expandedRange = activeRange.EntireRow; // selects entire rows and then I would like to create a multiselection only with the not-hidden rows... or if you prefer create a multiselection according to the active filter. I tried: Excel.Range selectedRange = null; foreach (Excel.Range row in expandedRange.Rows) { if (!row.Hidden) selectedRange = Globals.ThisAddIn.Application.Union(selectedRange, row); } but this gave a runtime error... Could anyone help me? |
Excel VSTO: Selecting range according to properties
I found the following solution:
// Gets the selected range Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range activeRange = Globals.ThisAddIn.Application.Selection.Cells; Excel.Range expandedRange = activeRange.EntireRow; // selects entire rows List<string laddr = new List<string(); foreach (Excel.Range r in expandedRange.Rows) // this returns only the rows not hidden if (!r.Hidden) laddr.Add(r.Address[true,true]); Excel.Range selectedRange = activeWorksheet.Range[string.Join(";", laddr)]; selectedRange.Select(); So I get the string representing the range in each row and then use Range[] to obtain the desired range object. |
All times are GMT +1. The time now is 04:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com