Steve has attended:
Excel VBA Intro Intermediate course
CurrentRegion Property
Anthony,
Excellent course last week. I raised the issue of the use of a range reference in conjunction with the CurrentRegion property. The line of code you were using was:
Sheets("MyData").Range("A2").CurrentRegion.......
Although Range A2 has been specified as the starting point from which to capture the data, this is being ignored and all data in the sheet starting from range A1 is being selected.
How do you overcome this to capture all data in a sheet starting at a specific cell.
Thanks.
RE: CurrentRegion Property
Hello Steve,
There are numerous ways to achieve what you are asking. The problem we have is CurrentRegion is reliable in selecting a range even if there are missing cell entries, but it selects the top row despite setting the starting cell as A2. This is because CurrentRegion will select across and down until it finds an entire empty column or row.
One way to work around this is to try the following code. It selects everything, counts the rows and deducts 1, this value is stored in variable.
We then use the resize and offset commands with this variable. This would work.
Dim counter As Integer
counter = Range("A2").CurrentRegion.Rows.Count - 1
Range("a1").CurrentRegion.Resize(counter).Offset(1, 0).Select
Some may ask why we don't just use the xlDown command, this would only work if the colulmn we are selecting down does not have any missing cell entries!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
