How do I create an Excel Range that is....

[David Thielen]

Hi;

Ok, lets assume I have a spreadsheet that uses some (not all cells) in
B1:H7. But many cells are empty including A1 and H7 - the only cell in column
H is H3 and the only cell in row 7 is E7.

1) Worksheet.UsedRange is then A1:H7 - correct? In other words, any
non-empty cell is inside UsedRange?

2) If I am on a cell, for example, C5, I need to do a search from C5 to H7
in row major order. I can't do Worksheet.get_Range(C5, H7) because that will
miss A6:B7. How can I set a Range to do a Find on that will first search
C5:H5 and then A6:H7? I can do 2 searches but that is inefficient.

3) I have the same thing where I need to do a previous search from C5 back
to A1. So first it needs to search A5:C5 and the A1:H4.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jialiang Ge [MSFT]]

Hello Dave,

1) Worksheet.UsedRange is then A1:H7 - correct? In other words,
any non-empty cell is inside UsedRange?

I do not think that the UsedRange is A1:H7. Worksheet.UsedRange refers to
the cells from the upper-left-most used cell to the lower-right-most used
cell. You mentioned that the cell A1 and H7 are empty, and the only cells
with value are H3 and E7. Therefore I think the UsedRange should be E3:H7.

2) If I am on a cell, for example, C5, I need to do a search from C5 to
H7 in row major order. I can't do Worksheet.get_Range(C5, H7) because that
will miss A6:B7. How can I set a Range to do a Find on that will first
search C5:H5 and then A6:H7? I can do 2 searches but that is inefficient.

My understanding of the question is that you want to search cells in C5:H5
first, then find other cells in A6:H7. Am I right?
As you said, run 2 searches could resolve the problem. Another possible
workaround is to run one search on the *union* range of C5:H5 and A6:H7 by
row, and set the start position as C5, so that it will find C5:H5 first,
then A6:H7.

3) I have the same thing where I need to do a previous search from C5
back to A1. So first it needs to search A5:C5 and the A1:H4.

It is the same issue as question 2. Running 2 searches is a method. Another
workaround is to run one search on the union range of A5:C5 and A1:H4 by
row, and set the start position as A5.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

Hi;

I did the two ranges and then a Union of them and that worked fine. One
note, the ranges for a prev find are searched in the order of their areas so
make sure you union them in in search order (row major in our case).

Here's the code in case anyone else needs it:

// if we are not at col 1 (forward) or N (previous) then we have to union
the block from the selection to the
// end/start and then the full width block from the next/previous row to
the end/start.
// The prev find needs the areas in search order. We do the next find
that way just to be safe.
Range searchRange, startAfter;
if (forward)
{
if ((selOn.Column == 1) || (selOn.Row == ws.UsedRange.Rows.Count))
searchRange = ws.get_Range(selOn,
ws.UsedRange.Cells[ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count]);
else
{
// this is a block where the left column is selOn.Column
searchRange = ws.get_Range(selOn, ws.UsedRange.Cells[selOn.Row,
ws.UsedRange.Columns.Count]);

// now get the block starting at "A:row+1" -> usedRange end
Range fullRows = ws.get_Range(ws.UsedRange.Cells[selOn.Row + 1, 1],
ws.UsedRange.Cells[ws.UsedRange.Rows.Count,
ws.UsedRange.Columns.Count]);
searchRange = app.NativeApplication.Union(searchRange, fullRows,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
startAfter = (Range)ws.UsedRange.Cells[ws.UsedRange.Rows.Count,
ws.UsedRange.Columns.Count];
}
else
{
if ((selOn.Column == ws.UsedRange.Columns.Count) || (selOn.Row == 1))
searchRange = ws.get_Range(ws.UsedRange.Cells[1, 1], selOn);
else
{
// get the row on A:N -> selOn:N
searchRange = ws.get_Range(ws.UsedRange.Cells[selOn.Row, 1], selOn);
// now get the block from A:1 -> "UsedColumns.Count:row-1"
Range fullRows = ws.get_Range(ws.UsedRange.Cells[1, 1],
ws.UsedRange.Cells[selOn.Row - 1, ws.UsedRange.Columns.Count]);
searchRange = app.NativeApplication.Union(searchRange, fullRows,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
startAfter = (Range) ws.UsedRange.Cells[1, 1];
}

Range rtn = searchRange.Find(text, startAfter, XlFindLookIn.xlValues,
XlLookAt.xlPart, XlSearchOrder.xlByRows,
forward ? XlSearchDirection.xlNext :
XlSearchDirection.xlPrevious, false, false,
Type.Missing);

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm