Hi folks,
to set the current region as column A (1,1) works
But I need Column D Cell 2 ie D2
Set otest = ActiveSheet.Cells(2, 4).CurrentRegion
Also what about the next column ie E2 in relation to this current region
is this correct?
sl
Hi folks,
to set the current region as column A (1,1) works
But I need Column D Cell 2 ie D2
Set otest = ActiveSheet.Cells(2, 4).CurrentRegion
Also what about the next column ie E2 in relation to this current region
is this correct?
sl
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 1Bee1 2Dee2 3 4Bee4 EFor 5 6SeeSicks
Sheet2
Using Excel 2007 32 bit
Row\Col A B C D E F G H 1Bee1 E Won Gees Won Two Sicks 2Dee2 FToo GeesWonTwoSicks 3GeesWonTwoSicks 4Bee4 EFor GeesWonTwoSicks 5GeesWonTwoSicks 6See 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 1Bee1 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 1Bee1 2Dee2 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 1Bee1 2Dee2 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
Last edited by Doc.AElstein; 09-13-2016 at 10:25 AM.
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
Hi Alan,
thanks for taking the time and effort to write up this in depth guide.
I am really bad with current regions - though I try.
I will need some time to digest it all and come to my own understanding.
I was in essence trying to only target D2 column and E2 Column, but the current region function is very perplexing to me
oh well
thanks again for your stellar response and guide
cheers
sl
Hi Sl
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 )
Hi Alan,
I was trying to learn how to use the current region as i see it in various macros.
I avoid these macros as I always get stuck on the current region, and then you have the offset and it gets a bit messy.
In this Example I was testing to see if I could list some cells in Column D to format in current worksheet.
Column D - Cells to Format | Column E - Font Name
H122 | Candara
X124 | Arial
The below is just for illustration - I dont need a working version.
It would be useful to know if I targeted the current region cells in the correct manner - which I have not.
There are easier ways to do what I am trying to do - but it would be nice not to run every time I see the current regionPlease Login or Register to view this content.
thank you
sl
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 _...
_... return exactly the same Range Object, shown as I did before:..Please Login or Register to view this content.
Using Excel 2007 32 bit
Row\Col C D E F 1H122 Candara 2X124 Arial 3 So your returned Range object is for both
Sheet2
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
Last edited by Doc.AElstein; 09-13-2016 at 01:09 PM.
Hi Alan,
thanks for sharing your wisdom.
Excel is incredibly complex and has its quirks, especially when it malfunctions thanks to newbie coders
who always come up with syntactically incorrect code.
I think we can be forgiven, part of our code growing pains.
When I tested the current region on a basic cell yesterday it selected something completely different,
which perplexed me no end - hence posting this thread.
Thanks for helping out with all the code snippets.
I can play about with them and get to grips with basic cells and then ranges, and then current regions when I select a column
Have a great day!
sl
You is welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks