VBA user defined range and check if it is empty

Question

I have been struggling with this for over an hour. I need to write a VBA code where the user selects a range and then I check if this selected range is empty before I go and do anything else.

This is what I have so far:

Sub test()
    Set rng= Application.InputBox("Select the range of the raw data please", Type:=8)
    If Application.WorksheetFunction.CountA(Range(rng)) > 0 Then
        MsgBox "do this, this and that!"
    End If
End Sub

When I run this I get a "Method Range of object_Global failed". I know it lets the user select the range just fine but the Range(rng) is not working right. Any help would be appreciated!

Range(rng) is redundant. Just write it as Application.WorksheetFunction.CountA(rng) — dwirony
– dwirony, Commented Jul 26, 2019 at 18:43

pheeper · Accepted Answer · 2019-07-26 18:44:37Z

2

Your problem is that your variable rng is a range and you're trying to wrap that in a range, which is why it's throwing an error. Try this instead.

Sub test()    
    Dim rng As Range
    Set rng = Application.InputBox("Select the range of the raw data please", Type:=8)

    If Application.WorksheetFunction.CountA(rng) > 0 Then
        MsgBox "do this, this and that!"
    End If

End Sub

answered Jul 26, 2019 at 18:44

pheeper

1,5475 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pheeper Over a year ago

@NadezhdaKarkelanova - great, happy to help. Since this answers your questions, could you please click the check mark next to my response to accept it as the answer?

SMJ · Accepted Answer · 2019-07-26 18:56:11Z

0

Just some code

 Sub main()

    Dim sentRange As Range

    Set sentRange = Application.InputBox("Select the range of the raw data please", Type:=8)


    If isRangeEmpty(sentRange) = False Then
        MsgBox "Range is not empty."
    Else
        MsgBox "Good to go!"
    End If


End Sub


Function isRangeEmpty(ByRef myRange As Range) As Boolean
    Dim rngLoop As Range
    Dim rangeEmpty As Boolean

    For Each rngLoop In myRange
        If rngLoop.Value = Empty Then
            'All Good
            isRangeEmpty = True
        Else
            'Need to exit
            isRangeEmpty = False
            Exit For
        End If
    Next rngLoop

End Function

edited Jul 26, 2019 at 18:56

answered Jul 26, 2019 at 18:54

SMJ

13 bronze badges

11 Comments

Mathieu Guindon Over a year ago

Why would myRange be passed ByRef?

SMJ Over a year ago

I always pass ByRef instead of ByVal for data that could be large. You could pass ByVal as you don't need the reference to actually change any data with the function.

Mathieu Guindon Over a year ago

You're passing an object pointer that's exacly, uhm, I'd guess a huge 4 bytes.

Mathieu Guindon Over a year ago

hm, just noticed the edit on the above comment. I'm sorry my "4 bytes" comment came across as snark. BTW FreeMan never got your ping, you need to @ them for that. As for passing a copy of an object, the only way to do that is to copy the object (which isn't possible for a Range object, a class that the user code doen't own and can't New up), then pass the copy - no parameter modifier deep-clones any object in VBA.

SMJ Over a year ago

Thanks for the info @MathieuGuindon . And that explanation helps. Would you explicitly say ByRef for objects to keep everything at a level someone later not extremely versed could still have the readability? Or leave it as a blank for objects like a range? Through this dialogue, though, I've definitely learned a bit! Something I came across in looking into info from these comments for objects of reference type was when using ByVal you cannot change the objects 'identity', but can change the values. ByRef allows change of the identity of the object.

|

urdearboy · Accepted Answer · 2019-07-27 00:17:18Z

0

If you are only acting on the instance of data being present then something like below will work. I would also adding Option Explicit to the top of your code and declare all variables.

Sub How()

Dim rng As Range
Set rng = Application.InputBox("Select Target Range", Type:=8)

If Application.WorksheetFunction.CountA(rng) <> 0 Then
    'Actions
End If

End Sub

answered Jul 27, 2019 at 0:17

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Collectives™ on Stack Overflow

VBA user defined range and check if it is empty

3 Answers 3

1 Comment

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related