Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you test that a Range in Excel has cells in it?

I've found a problem in Excel/VBA in the Worksheet_Change Event. I need to assign Target.Dependents to a Range, but if it has no dependents it brings up an error. I tried testing Target.Dependents.Cells.Count but that didn't work. Any Ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 OR Target.Dependents.Cells.Count = 0 Then Exit Sub

Dim TestRange As Range

Set TestRange = Target.Dependents

I've also tried "Target.Dependents Is Nothing".

like image 483
Lance Roberts Avatar asked May 28 '09 23:05

Lance Roberts


People also ask

How do you check if a cell is in a range in Excel?

Select the range of cells that you want to search. To search the entire worksheet, click any cell. On the Home tab, in the Editing group, click Find & Select, and then click Find. In the Find what box, enter the text—or numbers—that you need to find.

How do you check if a range of cells is not empty in Excel?

Excel ISBLANK function The ISBLANK function in Excel checks whether a cell is blank or not. Like other IS functions, it always returns a Boolean value as the result: TRUE if a cell is empty and FALSE if a cell is not empty. Where value is a reference to the cell you want to test.

How do you check if multiple cells are filled in Excel?

The below formula can help you check if a range of cells is blank or not in Excel. Please do as follows. 1. Select a blank cell, enter formula =SUMPRODUCT(--(G1:K8<>""))=0 into the formula bar, and then press the Enter key.


1 Answers

Short answer, there is no way to test for dependents without raising an error, as the property itself is set to raise an error if accessed and there aren't any. I dislike the design but there is no way to prevent it without suppressing errors. AFAIK this is about the best you are going to be able to do with it.

Sub Example()
    Dim rng As Excel.Range
    Set rng = Excel.Selection
    If HasDependents(rng) Then
        MsgBox rng.Dependents.Count & " dependancies found."
    Else
        MsgBox "No dependancies found."
    End If
End Sub

Public Function HasDependents(ByVal target As Excel.Range) As Boolean
    On Error Resume Next
    HasDependents = target.Dependents.Count
End Function

Explanation, if there are no dependents an error is raised and the value of HasDependents stays unchanged from the type default,which is false, thus false is returned. If there are dependents, the count value will never be zero. All non-zero integers convert to true, so when count is assigned as the return value, true is returned. It's pretty close to what you are already using.

like image 142
Oorang Avatar answered Oct 02 '22 00:10

Oorang