5

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.

Community
  • 1
  • 1
Kenny
  • 1,902
  • 6
  • 32
  • 61

1 Answers1

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. =/

Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
George Terziev
  • 129
  • 1
  • 3
  • 14
  • Glad to have your input, and it's never too late to learn something new. ;) Regarding your answer, it's exactly what I want to avoid : iteration and for loop. It would hurt performance, imaging one row or column in Excel which already contains 1 million cells; then multiply that by several rows/columns if I want to check multiple. That is the reason for built-in treatement like .Specialcells I suppose, it gives better performance according to my experience. – Kenny Sep 10 '15 at 09:35