Non-empty cells in Excel Range C#

Question

I want to select all non-empty cells from the current cell to the last cell of the row.

If we use

currentRange.End[XlDirection.xlToRight]

this is not a good approach when the non-empty cells are not contiguous link. For example, cells A1, A2, A3, A5, A6 are not empty, then RangeA1.End[XlDirection.xlToRight] only go as far as A3, that is, the last non-empty cell connected to RangeA1.

Another option:

CreateRange(currentRange, lastRangePossibleinRow).SpecialCells(...)

I have 3 question:

1. currentRange.End[XlDirection.xlToRight] seems like an unreliable solution. For example, if currentRange is empty, this will return 1 cell anyway (itself).

2. How do I extend the selection from current cell to the last cell in Row effectively? CreateRange(currentRange, get_range(currentRange.Row + sheet.Columns.Count.ToString())) ? Perhaps there is a better solution.

3. How to get a collection of cells in this Range that is not empty? .SpecialCells(xlCellTypeConstants | xlCellTypeFormulas) will not work, for example, if non is found, .SpecialCells(xlCellTypeConstants) will throw an exception: no cells found.

Answer 1

I'm new to VBE, but I think a workaround would be to

1. target a range in which you expect non-blanks
2. iterate through them
3. check if isblank
4. store the col# of the ones that are false in an array in the form RC[#]
5. then select just those by Range("Array(1),Array(2),...").Select .

Sorry for the pseudocode... and being two months late. =/