CurrentRegion issue (attempt 2)

[Barb Reinhardt]

Let's try this again.

I have the following code:

Set mySourceRange = mySourceRange.CurrentRegion

where mySourceRange is already a defined range. There is a region of data
that's bounded by blank cells, but it only seems to find the first row of
data and I can't seem to figure out why.

Can someone assist?

Thanks,
Barb Reinhardt

 

[Tim Zych]

Is this in a UDF called from a worksheet cell? If so it can't use
CurrentRegion.

 

[Sandusky]

Let's try this again.

I have the following code:

Set mySourceRange = mySourceRange.CurrentRegion

where mySourceRange is already a defined range. There is a region of
data
that's bounded by blank cells, but it only seems to find the first row of
data and I can't seem to figure out why.

Can someone assist?

Thanks,
Barb Reinhardt

Try clearing range object first. If MySourceRange is already assigned a
range object, then MySourceRange.CurrentRegion will try to find the current
region with the already defined range.

Or...do you have any rows hidden? Any AutoFilters in place?

 

[JLGWhiz]

Barb, This worked for me.

Sub currgn()
Set myRng = Range("C9:C11")
myRng.CurrentRegion.Interior.ColorIndex = 3
MsgBox "Take a look"
myRng.CurrentRegion.Interior.ColorIndex = xlNone
End Sub

So it has nothing to do with having a previously defined range, apparently.
Must be some other quirk.

 

[JLGWhiz]

Let me try this again. I set up an area of about 10 colums by 12 rows. Then
I used this macro with the variable re-set to include CurrentRegion and it
worked OK.


Sub currgn()
Set myRng = Range("C9:C11")
Set myRng = myRng.CurrentRegion
myRng.Interior.ColorIndex = 3
MsgBox "Take a look"
myRng.CurrentRegion.Interior.ColorIndex = xlNone
End Sub

 

[sebastienm]

Hi,
I believe CurrentRegion is based on the first cell of the range object, i.e.
mySourceRange.CurrentRegion in fact does
mySourceRange.cells(1).CurretRegion
That is, even through mySourceRange contains 100 rows of data and 20
columns, if the second row is blank, CurrentRegion returns the first row only.
Are you in this case?

 

[Barb Reinhardt]

I discovered that my problem was a "user error" (mine).
Thanks to all,
Barb Reinhardt