Column D Current Region

Ranging from Cells to Member Areas. ‘A Member Wonks. EP .CurrentRegion Property

Hi sl__
It is not clear to me exactly what you are asking.
But possibly this will help ...

The VBA Range .CurrentRegion Property of a Range Object ( I note that You are using a single Cell Object, but that is also a Range Object and so Range Object Properties may be applied to it) , returns a new Range Object which is that of a “box” or rectangular area of contiguous cells which, ( starting from the Top Left of the Range Object, ( The Cell Object in your case ) ), extends until it “touches” no more cells with anything in them. In other words, the .CurrentRegion Property of a Range Object returns a new Range Object which is the first ( starting looking from the Top Left ) rectangular area ( “box” ) bounded by empty Cells, or the spreadsheet boundaries, that is to say bounded by a rectangle perimeter which is built from a 1 cell “width” or the Spreadsheet boundaries

For example , In the following examples,
Worksheets("Sheet2").Cells(2, 4).CurrentRegion
returns the Range Object shown in Yellow. In Red is shown the Bounding Perimeter Cells.

Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F
1		Bee1
2				Dee2
3
4		Bee4			EFor
5
6			SeeSicks

Sheet2

Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F	G	H
1		Bee1			E Won		Gees Won Two Sicks
2				Dee2		FToo	GeesWonTwoSicks
3							GeesWonTwoSicks
4		Bee4			EFor		GeesWonTwoSicks
5							GeesWonTwoSicks
6			See Sicks				GeesWonTwoSicks
7

Sheet2

So in the above examples, I “start looking around me” from cell D2 and find the first enclosing empty rectangular Perimeter ”built” with cells “building blocks” or the Worksheet boundary
_........................__________


The Range Object and Range Object Properties are probably one of the most important things to get to grips with. But it can be tricky
Take a look at these Members:...
https://msdn.microsoft.com/en-us/lib.../ff197454.aspx
CellRangeAreaMembers.JPG
http://imgur.com/j60h99e

1) Cells and Range Property Members
_....One thing that can really confuse is that The Range Object has a Range Property and a Cells Property. Those two properties can be used to adjust “ where you are “ or “ you get returned “ etc..etc... .....

_1a ) Cells property. Consider again the first Spreadsheet example, but I use this code line now

Worksheets("Sheet2").Cells(2, 4).Cells(0, -1).CurrentRegion

Here the Cells Property is applied to the Range Object D4 ( your Cell Object Cell(2, 4 ) ). That takes us one cell back up, and two cells back to the left returning the Range Object B1. Applying the CurrentRegion Property to that new Range Object returns us the single cell Range Object, as shown again in yellow:

Using Excel 2007 32 bit

Row\Col	A	B	C
1		Bee1
2

Sheet2

Note the subtle convention there:
Property Cell( 1, 1 ) “stays where you are”, - First cell Top left of where you start;
Property Cell( 0, 0 ) “ takes you one back left and one back up”
http://excelmatters.com/referring-to-ranges-in-vba/ ..... )

_...

_1b) Range Property
Take the above one step ( or Property ) further and we reveal something quite interesting, ( depending on your Member preferences...)
Consider this code line
Worksheets("Sheet2").Cells(2, 4).Cells(0, -1).Range("A1:B2").CurrentRegion
The Range Property .Range("A1:B2") now “works” on the Range Object, B1, ( which we obtained in _1a) ) to return us the new Range Object starting at B1 and extending over the 4 cell rectangular area got by extending 2 to the right and 2 down, ( looking from cell B1 ) shown in Green here:

Row\Col	A	B	C	D
1		Bee1
2				Dee2
3

Here is a subtlety:
_ 1c) Member .CurrentRegion v .Value applied to the same Range Object
You might have expected the .CurrentRegion Property now to extend such as to cause the .CurrentRegion Property to “start” in the area B1:C2 and then extend out so as to include the Cell D2. I know I did!! _---
_---I do know that if I apply the .Value Property instead to the same Range, such_..
Worksheets("Sheet2").Cells(2, 4).Cells(0, -1).Range("A1:B2").Value
_...then I get returned back a Field ( Array ) containing 4 Elements with the values of those cells, B1:C2, in them.
But In fact for the .CurrentRegion Property applied to the same Range Object
Worksheets("Sheet2").Cells(2, 4).Cells(0, -1).Range("A1:B2").CurrentRegion
I am retuned just the cell B1 again.

Even if I extend my Range Object over a massive Area. With this_..
Worksheets("Sheet2").Cells(2, 4).Cells(0, -1).Range("A1:ZZ10000").CurrentRegion
_... I am still returned just Cell B1

Once Again the answer is: “....CurrentRegion Property of a Range Object returns a new Range Object which is the first starting looking from the Top Left rectangular area ( “box” ) bounded by empty Cells, or the spreadsheet boundaries....”

_.....

_2) .CurrentRegion Property Member ( Applied to entire Worksheet )
May be this helps again to answer your main Question.

Going back to my first screen shot. Use either of these to apply the .CurrentRegion to the entire Worksheet. ( For XL 2007 + ) with either of these lines_...
Worksheets("Sheet2").Range("A1:XFD1048576").CurrentRegion
Worksheets("Sheet2").Cells.CurrentRegion
_.... the resulting retuned Range Object is A1:B1 as shown in yellow :

Row\Col	A	B	C	D
1		Bee1
2				Dee2
3

note: I “started looking around me from A1” . ( I was “looking from” Top Left ( Cell A1 ) ) so did not look at Cell A1 and could not therefore consider that cell ( I would have “looked at it” if I looked from cell B2 as I did in a previous example )

So to re iterate what is going on one last time. The .CurrentRegion Property applied to a Range Object will return a Range Object that is the first one it “sees” ( starting looking from the Top Left of the Range Object ) that can be enclosed in a rectangular perimeter made from Cells of “width” of one cell, or a combination of that and the Worksheet Boundaries. Not a lot of people know that I expect: The documentation is crap, and I only “discovered it” as I tried to answer this Thread

_...............

_3) Member Areas

A last point, just for completeness. Strictly speaking a Range Object is not a rectangular Area of contiguous cells. It can include many such areas across a single Worksheet. Strictly speaking, the code lines I used above are not correct., or not Explicitly Pedantic enough.. But as often, VBA guesses what you mean. In this case it assumed there was only one rectangular area in the Range Object, so it referenced the Area within that Range Object having the Item number of 1, corresponding to the first ( and only one in this case ) assigned to the Range Object. VBA actually interprets the first code line as
Worksheets("Sheet2").Cells(2, 4).CurrentRegion as Worksheets("Sheet2").Cells(2, 4).Areas.Item(1).CurrentRegion.
You can check that in any codes you may have by including the .Areas.Item(1) . you will see that that inclusion will do no harm..
http://www.excelforum.com/showthread...t=#post4398645


_.....................

Hope that was some insight.. ( it was for me .. )...

Alan


Rem Ref
http://excelmatters.com/referring-to-ranges-in-vba/
http://www.excelforum.com/excel-prog...e-objects.html
http://www.excelforum.com/showthread...t=#post4398645
http://www.excelforum.com/excel-new-...to-arrays.html
http://www.mrexcel.com/forum/excel-q...-anomaly.html?
http://www.mrexcel.com/forum/excel-q...e-anomaly.html

Hi Sl

Originally Posted by sl729

....
I am really bad with current regions - though I try.
_....
thanks again for your stellar response and guide
_...

Thanks for the Feedback. I thought I knew what the .CurrentRegion did. Then as I tried to answer your question I realised that did not. I think I do now. None of the literature says what I learned and found out !!

There are infinite ways to “get at” or reference two columns. The CurrentRegion may not be the best way. Usually you just find out the last row in the columns you are interested in. That is easy. Then you reference your range as up to that last row.

If you try to show exactly what you are trying to do, then we may be able to suggest something better...

But it does no harm to digest the CurrentRegion Info. If you mange to understand what I wrote, then we may be the only two people that fully understand it. .. L0l.. Many professionals told me it was the Range extending to cover all cells connected to through cells with something in them to the Range Object it is applied to. I found out when I experimented Today that that was wrong ). The key is
“Look from the Top Left Cell of the Range object to which I apply the .CurrentRegion to,
( that was D2 in your example )
Find the first clear Perimeter ( what I showed in Red )
Then the range within that perimeter is my returned Range Object.”


Alan

( P.s. If you want to “show” us then check out for example the last bit here_....
http://www.excelforum.com/showthread...post4478582For _....for suggested ways to do that )

From the syntax point of view, your use of CurrentRegion is fine.

I think once you have digested what I wrote about Current Region, then you will probably see that you are doing something a bit strange.. Lol... ( But I could be talking rubbish if Ihave not twigged to exactly what you are doing )

In your code, both these lines _...

Please Login or Register  to view this content.

_... return exactly the same Range Object, shown as I did before:..

Using Excel 2007 32 bit

Row\Col	C	D	E	F
1		H122	Candara
2		X124	Arial
3

Sheet2

So your returned Range object is for both
Range("D1:E2")
that is to say, you get
Set oCellRng=Range("D1:E2")
Set oCellTarget=Range("D1:E2")


I am not sure if that is what you intended ?

I cannot see what you want to do exactly with the rest of your code. It makes no sense from the syntax point of view.

Possibly you are wanting to use the CurrentRegion to determine the Ranges of column D and E up to the last entry. That is not often done with the CurrentRegion , as there is the danger it will catch a bigger Area then you want, as in my second screen shown in Post #2. In your simple example it would work_...... ( assumes both ranges start and stop in the same row and that there are no empty rows between the first and last used rows ) _.... Like this:

Please Login or Register  to view this content.

Alan

You is welcome