Hiding Multiple Rows In Excel In One Command?

Crappy Name

Hi,

I am just doing some VSTO Excel code and I need to be able to hide a list of rows. Making individual calls to do this in a loop is REALLY slow (I'm assuming because of the COM interop ), so I was wondering if there was a way to do it in one command, maybe by building the list of rows in c# and passing it to excel somehow, but I'm not overly familiar on how to manipulate the range object in C#.

I would be grateful for any suggestions. At the moment, but function looks like...

//
// function to filter rows that do not match criteria
//
private void HideRows(Excel.Range in_range, string in_filterString)
{
// loop through range
foreach (Excel.Range row in in_range.Rows)
{
// does this match the criteria
Excel.Range cell1 = (Excel.Range)row.Cells[1,1];
if ((string)cell1.Text == in_filterString)
row.EntireRow.Hidden = false;
else
row.EntireRow.Hidden = true;
}
}

Dennis Wallentin

Crappy Name,

The following will improve the performance considerable:

Dim rnData As Excel.Range = Me.Range("E10:E200")

Dim rnFind As Excel.Range

Dim stAddress As String

Me.Application.ScreenUpdating = False

With rnData

rnFind = .Find(What:="Test")

If Not rnFind Is Nothing Then

stAddress = rnFind.Address

Do

rnFind.EntireRow.Hidden = True

rnFind = .FindNext(rnFind)

Loop While Not rnFind Is Nothing And rnFind.Address <> stAddress

End If

End With

Crappy Name

Thanks. This was quicker, but still VERY slow. I found a way to do it minimizing my COM interop calls. Took a while to figure out, but check it out; it's instantaneous...

//
// function to filter rows that do not match criteria
//
private void HideRows3(Excel.Range in_range, string in_filterString, string in_filterString2, string in_filterString3)
{
string hideRef = null;

// convert to .net object to minimize com interop
Object[,] values = (Object[,])in_range.Value2;

// unhide all rows by default
in_range.EntireRow.Hidden = false;

// hide rows
bool hiding = false;
int startHide = 0;
for (int i = 1; i <= values.GetLength(0); i++)
{
// if we did not find match
if ((string)values[i, 1] != in_filterString || (string)values[i, 2] != in_filterString2 || (string)values[i, 3] != in_filterString3)
{
// if not currently hiding, set start
if (!hiding)
{
// keep start pos
startHide = i;

// now hiding
hiding = true;
}
}
else
{
// were we hiding
if (hiding)
{
// add to list
if (string.IsNullOrEmpty(hideRef))
hideRef = "A" + startHide.ToString() + ":A" + (i-1).ToString();
else
hideRef += ",A" + startHide.ToString() + ":A" + (i-1).ToString();

// no longer hiding
hiding = false;
}
}
}

// add last reference if we were hiding
if (hiding)
{
// add to list
if (string.IsNullOrEmpty(hideRef))
hideRef = "A" + startHide.ToString() + ":A" + values.GetLength(0).ToString();
else
hideRef += ",A" + startHide.ToString() + ":A" + values.GetLength(0).ToString();
}

try
{
// hide all rows in one command!
Excel.Range hideRange = (Excel.Range)in_range.get_Range(hideRef, System.Type.Missing);
hideRange.EntireRow.Hidden = true;
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message + " hiding rows");
}
}

Dennis Wallentin

Interesting, how many rows are actually in use

Crappy Name

At this point 600. Before it was taking about 15 seconds, now it's instant.