How to tell whether any cell within a Range has non-empty contents

[Cullen Morris]

I am writing a VSTO addin for Excel 2003 that reads data from a
database and populates a range of cells. I want to be able to detect
if there is any existing data within any of the cells in the range, so
that I can prompt the user whether to overwrite or insert rows/
columns. The amount of data returned could be different each time.

Is there a method that will tell me if any cell in a range is occupied
(including a formula), and which cell it is within the range?

Thanks for the help.

 

[Héctor Miguel]

hi, Cullen Morris !

from within excel, I would use something like the following:

Dim CellsInUse As Range
With Range("a1:d30")
On Error Resume Next
Set CellsInUse = Union( _
.SpecialCells(xlCellTypeConstants), _
.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
End With
If CellsInUse Is Nothing Then Exit Sub
MsgBox "There are non-empty cells in the range !!!" & _
vbCr & CellsInUse.Address
Set CellsInUse = Nothing

hth,
hector.

__ OP __

 

[Cullen Morris]

hi, Cullen Morris !

from within excel, I would use something like the following:

  Dim CellsInUse As Range
  With Range("a1:d30")
    On Error Resume Next
    Set CellsInUse = Union( _
      .SpecialCells(xlCellTypeConstants), _
      .SpecialCells(xlCellTypeFormulas))
    On Error GoTo 0
  End With
  If CellsInUse Is Nothing Then Exit Sub
  MsgBox "There are non-empty cells in the range !!!" & _
    vbCr & CellsInUse.Address
  Set CellsInUse = Nothing

hth,
hector.

__ OP __

Thanks for the help!