Check for empty range in vba
 

Hi all,
I have a workbook with a number of worksheets that contain data about
staff development objectives. Each person has their own worksheet and
I've developed a macro that pulls all the data into one summary sheet.

Because the number of rows of data in each sheet will vary from month
to month, there is a dynamic range name for each and the macro selects
the data for the summary using that.

All works fine, except for occasions when there is no data in the
dynamic range, for instance when a person has no objectives for that
month. In this case the macro falls over because the range is empty.

What I need to know (and I apologise for my limited VBA knowledge
here!), is how to check to see if the range is empty and if so, to
ignore this range and move on to the next.

It seems simple enough, and I understand just about enough to believe
it probably needs some kind of loop and a conditional
formula..........but I don't understand enough to make it happen!

Any help would be most gratefully received

Check for empty range in vba
 

You can test for an empty range as follows:


Sub Macro1()
Dim r As Range
Call macro2(r)
Set r = Range("A1")
Call macro2(r)
End Sub




Sub macro2(r As Range)
If r Is Nothing Then
MsgBox ("r is nothing")
Else
MsgBox ("r is " & r.Address)
End If
End Sub

--
Gary's Student


"Phil" wrote:

Hi all,
I have a workbook with a number of worksheets that contain data about
staff development objectives. Each person has their own worksheet and
I've developed a macro that pulls all the data into one summary sheet.

Because the number of rows of data in each sheet will vary from month
to month, there is a dynamic range name for each and the macro selects
the data for the summary using that.

All works fine, except for occasions when there is no data in the
dynamic range, for instance when a person has no objectives for that
month. In this case the macro falls over because the range is empty.

What I need to know (and I apologise for my limited VBA knowledge
here!), is how to check to see if the range is empty and if so, to
ignore this range and move on to the next.

It seems simple enough, and I understand just about enough to believe
it probably needs some kind of loop and a conditional
formula..........but I don't understand enough to make it happen!

Any help would be most gratefully received