Range Problem in VB Editor

MillB · #1 (**permalink**) 05-12-08, 18:22

For some reason I get a runtime error when I specify a whole range, but if I do the cells individually it works. What I need is to be able set the
RANGE ("C13:N13" = "" Then

Does anyone know if I'm overlooking something, or could provide a possible solution?

See attached Screenshots for visual. Notice where the code breaks...that's where I'm stumped.

Thanks....

MillB

MikeTheBike · #2 (**permalink**) 05-14-08, 08:41

Hi

Yes it would seem this doesn't work with multi cell ranges.

I would suggest a little function (especialy if you use this more than once in your code) thus

Code:

Function RangeIsBlank(ByRef ThisRange As Range) As Boolean
    Dim cel As Range
    RangeIsBlank = True
    For Each cel In ThisRange
        If cel <> "" Then RangeIsBlank = False
    Next cel
End Function

Used like this

Code:

Sub test()
    If RangeIsBlank(Range("C13:N13")) Then
         MsgBox "Range is Blank"
         'etc
         'etc
    End If
End Sub

HTH

MTB

norie · #3 (**permalink**) 05-25-08, 14:52

MillB

You can't compare a single value to multiple values like that.

stuschmied · #4 (**permalink**) 05-27-08, 14:44

Hi,

I couldn't make out your screenshots so I tried this simple code and it worked fine for me. I can't tell by your post where you have the final paren but make sure its before the equal sign.

Private Sub TestRange()

Range("C13:N13") = "Test"
Range("C13:N13") = ""

End Sub

Good luck, Stu

Internet Services

Web Development

Digital Marketing

Consumer Tech