asked on

Test for a range that evaluates to nothing

Hi

I am using excel in office 2013. The following code fails when there are no constants in the selected range. It works fine if the range does not evaluate to nothing.

I need the following logic

If rStoreConstants <> nothing then do something

Dim RStoreConstants As Range
Set RStoreConstants = TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants)

Open in new window

[ fanpages ]

Hi,

You will need to ignore errors (with an On Error Resume Next statement above your existing code), but you may use this:

If Not (RStoreConstants Is Nothing) Then
rem Do Something here
End If

Martin Liss

If Not rStoreConstants Is Nothing Then

peispud

ASKER

I've used this code. A pop up comes up saying "No cells were found."

If Not TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants) Is Nothing Then
        Set RStoreConstants = TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants)
End If

Open in new window

Martin Liss

Are the cell values in the range the result of formulas?

peispud

ASKER

mixed. There are always formulas in the selected range. There may be constants.

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial