vba: Figure out if a changing range is blank

Question

I have two spreadsheets, one contains programs and one projects. I use D loops to look at projects, within programs, within countries. I am trying to figure out if a set of cells for each project is blank. I have tried a few things. In the case below sustTrue should stay at 0 if rangeVar is blank for all the projects, but it does not. Please help!

Sub NO_Sheet()

Sheets("Program_FINAL").Select
Range("C2").Select ' C column is country 
Dim IndicatorLineIterator
Do Until IsEmpty(ActiveCell) ' loop until country is blank
      IndicatorLineIterator = 61
      Dim PRGNum
      PRGNum = ActiveCell.Offset(0, -2).Value ' Identify the program number

      Sheets("Project_FINAL").Select
      Range("A2").Select ' A column is the project number

      Dim rangeVar, sustTrue
      sustTrue = 0
      Do Until IsEmpty(ActiveCell) ; loop until Project number is blank

            If PRJNum = ActiveCell.Value Then

                'rangeVar = ("O" & ActiveCell.Row & ":S" & ActiveCell.Row)
                rangeVar = Range(ActiveCell.Offset(0, 14) & ":" & ActiveCell.Offset(0, 17))
                If Not IsEmpty(rangeVar) Then
                    sustTrue = sustTrue + 1
                    MsgBox (sustTrue)
                End If

            End If
        ActiveCell.Offset(1, 0).Select
        Loop
End If

'Sheets(SheetADPName).Range("M16").Value = sustTrue

Sheets("Program_FINAL").Select
ActiveCell.Offset(1, 0).Select
Loop


End Sub

Further, you can't reliably use the `IsEmpty` on a range array, only a single cell or other simple data type. — David Zemens, Oct 23 '14 at 18:24

David Zemens · Answer 1 · 2014-10-23T18:48:17.337

Your rangeVar is not a range object.

Strongly type your variables:

Dim rangeVar as Range
Dim sustTrue as Long 'Or As Integer
Dim cl as Range

Then use the Set keyword to assign object variables and instead of concatenating, we can just list the two cells separated by a comma, and this will create the range:

Set rangeVar = Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 17))

Further, you can't reliably use the IsEmpty on a range array, only a single cell or other simple data type. You can get around this by iterating the range:

For each cl in rangeVar.Cells
    If Not IsEmpty(cl.Value) Then
        sustTrue = sustTrue + 1

    End If
Next
MsgBox (sustTrue)

The IsEmpty function may not be a foolproof way to do this check. But the rest of the logic above should help. Let me know if you have trouble with the IsEmpty part.

Another alternative would be to simply subtract the number of blanks from the total number of cells in that range (again, may not be 100% reliable if the cells aren't "truly" blank...)...

sustTrue = rangeVar.Cells.Count - rangeVar.SpecialCells(xlCellTypeBlanks).Cells.Count

I tried this and it gives me run time error 1004 "Metrhod 'Range; of object '_global' failed" on the set rangeVar line — spaindc, Oct 23 '14 at 18:40
My mistake, do this instead: `Set rangeVar = Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 17))` — David Zemens, Oct 23 '14 at 18:47

score 0 · Answer 2 · answered Oct 23 '14 at 18:36

I kept working on it and the following worked for me. There is probably a more streamlined way to do this, but this works!

                If Not ActiveCell.Offset(0, 14) = "" Or Not ActiveCell.Offset(0, 15) = "" Or Not ActiveCell.Offset(0, 16) = "" Or Not ActiveCell.Offset(0, 17) = "" Then
                    sustTrue = sustTrue + 1
                    MsgBox (sustTrue)
                End If

score -2 · Answer 3 · edited May 23 '17 at 10:24

-2

RangeVar is a range object. You would need to do Isempty(RangeVar.Value)

I think this has been answered already here: Using VBA to check if below cell is empty

edited May 23 '17 at 10:24

Community

1
1

answered Oct 23 '14 at 18:24

Jongscx

70
1
3
12

`RangeVar` is **not** an object in OP's code, even if that is the intention. – David Zemens Oct 23 '14 at 18:26
Ok, so rangevar should be in the form of "O#:S#" according to the commented out section. Isempty(Range(rangevar).value) then? – Jongscx Oct 23 '14 at 18:34

vba: Figure out if a changing range is blank

3 Answers3