Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Unanswered: Range Problem in VB Editor

    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
    Attached Thumbnails Attached Thumbnails ScreenShot1.png   ScreenShot2.png   ScreenShot3.png  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    MillB

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

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    It can't be this simple...

    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
    --If its free, take it for what its worth!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •