Passing Null Value to Range Property
 

I am setting a range ojbect to a specific range:

Set Rng1 = ActualUsedRange

ActualUsedRange is a public function which returns a range by selected
certain cells in a sheet. Now I know that when I am in a blank worksheet the
VBA UsedRange property as displayed in the Immediate Window comes up empty"

?Worksheets("Sheet1").UsedRange

There are instances where I will not have a range to pass to Rng1 above via
my ActualUsedRange function. For example in a blank worskeet. What error
handling do need to put in so that the following line of code will not fail.
The way I am currently doing it, I get a Runtime Error 424 Object Required.

For Each Cell In Rng1

Thanks

EM

Passing Null Value to Range Property
 

Hi. here's one way:


If Not Rng1 Is Nothing Then
For Each Cell In Rng1
Next
End If


--
Hope that helps.

Vergel Adriano


"ExcelMonkey" wrote:

I am setting a range ojbect to a specific range:

Set Rng1 = ActualUsedRange

ActualUsedRange is a public function which returns a range by selected
certain cells in a sheet. Now I know that when I am in a blank worksheet the
VBA UsedRange property as displayed in the Immediate Window comes up empty"

?Worksheets("Sheet1").UsedRange

There are instances where I will not have a range to pass to Rng1 above via
my ActualUsedRange function. For example in a blank worskeet. What error
handling do need to put in so that the following line of code will not fail.
The way I am currently doing it, I get a Runtime Error 424 Object Required.

For Each Cell In Rng1

Thanks

EM

Passing Null Value to Range Property
 

Isn't UsedRange always at least one cell, even in an empty worksheet.

Anyways, how about

If Not Rng1 Is Nothing Ten

Foe Each Cell In Rng1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ExcelMonkey" wrote in message
...
I am setting a range ojbect to a specific range:

Set Rng1 = ActualUsedRange

ActualUsedRange is a public function which returns a range by selected
certain cells in a sheet. Now I know that when I am in a blank worksheet
the
VBA UsedRange property as displayed in the Immediate Window comes up
empty"

?Worksheets("Sheet1").UsedRange

There are instances where I will not have a range to pass to Rng1 above
via
my ActualUsedRange function. For example in a blank worskeet. What error
handling do need to put in so that the following line of code will not
fail.
The way I am currently doing it, I get a Runtime Error 424 Object
Required.

For Each Cell In Rng1

Thanks

EM

Passing Null Value to Range Property
 

?Worksheets("Sheet1").UsedRange
is the same as:
?Worksheets("Sheet1").UsedRange.Value

If the sheet is empty, the .usedrange.address will be $A$1. And printing the
value of that single cell will show an empty string.

(If there is more than one cell in the usedrange, then that line will fail.)

You may want to use:
set rng1 = actualusedrange
if rng1.address = "$A$1" then
if isempty(rng1.value) then
msgbox "the sheet is empty"
end if
end if





ExcelMonkey wrote:

I am setting a range ojbect to a specific range:

Set Rng1 = ActualUsedRange

ActualUsedRange is a public function which returns a range by selected
certain cells in a sheet. Now I know that when I am in a blank worksheet the
VBA UsedRange property as displayed in the Immediate Window comes up empty"

?Worksheets("Sheet1").UsedRange

There are instances where I will not have a range to pass to Rng1 above via
my ActualUsedRange function. For example in a blank worskeet. What error
handling do need to put in so that the following line of code will not fail.
The way I am currently doing it, I get a Runtime Error 424 Object Required.

For Each Cell In Rng1

Thanks

EM

--

Dave Peterson