Geek Answers Handbook

Non-empty cells in Excel Range C #

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 a bad approach when non-empty cells do not touch the link . For example, cells A1, A2, A3, A5, A6 are not empty, then RangeA1.End[XlDirection.xlToRight] is suitable only for A3, that is, the last non-empty cell associated with RangeA1.

Another option:

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

I have 3 questions:

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

  • How to increase the selection of the current cell to the last cell in the row? CreateRange(currentRange, get_range(currentRange.Row + sheet.Columns.Count.ToString())) ? Perhaps there is a better solution.

  • How to get a set of cells in this range that is not empty? .SpecialCells(xlCellTypeConstants | xlCellTypeFormulas) will not work, for example, if it is not found, .SpecialCells(xlCellTypeConstants) will throw an exception: no cells were found.

+5
source share
1 answer

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

  • indicate the range in which you expect to unlock
  • iterate through them
  • check if isblank
  • save col # from those that are false in an array in RC[#] form RC[#]
  • then select only those specified in Range("Array(1),Array(2),...").Select .

Sorry for the pseudo-code ... and was two months late. = /

+1
source

More articles:


All Articles