Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 30th 20, 11:18 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 2
Default 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?


  #2   Report Post  
Old October 2nd 20, 12:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 2
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting two different columns in excel using VSTO Kiran Excel Programming 0 March 8th 10 02:24 PM
Range.Value2 in C# (Excel 2007, VSTO 2008)? Fabz[_2_] Excel Programming 2 December 8th 09 03:51 PM
Storing add-in properties with workbook - how to? (VSTO 2007) Thomas[_24_] Excel Programming 2 September 23rd 09 08:09 AM
Formulas assignment from array to range in VSTO Excel doesn't work vsto excel array to range Excel Worksheet Functions 0 December 11th 07 05:48 PM
VSTO: insert one row into a 1-row range jj_online Excel Programming 0 June 23rd 06 03:17 AM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017