What am I doing wrong? If 2 or more cells in range are not empty then delete all cells

[kingleviathan]

Hi Guys

I'm trying to write a real time/live command (not an on comman macro)....

I have a range of drop list....its possible to have more than one drop list selected even though the field itself may appear blank (by selecting a blank option)

Therefore I want to make sure that a max 1 (or 0) cells can be selected at one time.

For this reason =counta() is unsuitable as it will recognise drop boxes even they appear unselected.

Therefore I'm using this to count the blank cells
Range("Y1") = "=SumProduct((Len(D13:D18) = 0) * 1)"

I should be able to apply an if...then statement = ie..if there are 6 cells in range and 2 have been selected (therefore 4 or less cells are blank) then make all cells blank....

What am i doing wrong with below?

For Each cell In Range("D13:D18")
Range("Y1") = "=SumProduct((Len(D13:D18) = 0) * 1)"
' Count how many blank cells in range
If Range("Y1") <= 4 Then
'if 4 or less cell blank
Range("D13:D18").Value = " "
' make all cells in range blank
Range("y1").Value = "5"
'stops value going to 0 for next loop
End If
Next

 

[kingleviathan]

Hi Guys..

I would be most grateful if you could spare a minute of your valuable time and assist me with this problem that has me stumped.

If you can't solve it, maybe you can point me in the right direction to get me going...That would be great too

I'm not sure how clear my explanation above was..so will try explain again

I have a range of drop lists (ie..D13:D18)....so 6 Cells

I want to make sure that a maximum 1 (or 0) cells can have data from the range at one time.

I've found =counta() unsuitable as it will sometimes recognise drop boxes as being filled.

So I want to say........IF more than => 2 cells in range are NOT blank then make ALL CELLS in range Blank

OR

The reverse which I have done below...IF <= 5 are blank (in range of 6 cells) then make all cells blanks

But it doesnt seem to work...Can anyone help????

For Each cell In Range("D13:D18")
Range("Y1") = "=SumProduct((Len(D13:D18) = 0) * 1)" ' Count how many blank cells in range
If Range("Y1") <= 5 Then 'if 5 or less cell blank
Range("D13:D18").Value = " " ' make all cells in range blank
Range("y1").Value = "6" 'stops value in Y1 going to 0 for next loop
End If
Next