Blank cells in dynamic range

[724napier]

Hi All,

I am working on a program and run into a bit of a problem I am looking to get help with. I have a dynamic range that varies depending on user input. I am trying to add code that selects the range and ensures there are no blank cells. If a cell is found to be blank in the range I want to display a msg box and make the code stop. The code is shown below and the range I am trying to select is for fastLoc which is Columns C through E which is dynamic and changes based on numFast input by the user.

I don't think this should be hard, but I have been struggling to find a solution. Any help is much appreciated.

 For i = 0 To numFast - 1
        Dim length_o As Double
        fastLoc(i, 0) = Range("C" & firstFastenerRow + i)
        fastLoc(i, 1) = Range("D" & firstFastenerRow + i)
        fastLoc(i, 2) = Range("E" & firstFastenerRow + i)
        fastOrient(i, 0) = Range("G" & firstFastenerRow + i)
        fastOrient(i, 1) = Range("H" & firstFastenerRow + i)
        fastOrient(i, 2) = Range("I" & firstFastenerRow + i)
        length_o = (fastOrient(i, 0) ^ 2# + fastOrient(i, 1) ^ 2# + fastOrient(i, 2) ^ 2#) ^ 0.5

 

[Rhodie72]

If you load the range(s) into an array and then use loops to check the array for an empty value, you could easily return that fact to the user in a msgbox routine. Arrays can be complex but a simple range can be arrayed easily and checked. Have a look here
All you'd do is return a result to the user and remember to erase the array from RAM when you're done.

 

[Norie]

If you want to check for blank cells in a range you can use SpecialCells(xlCellTypeBlanks).

What range do you want to check?