Tuesday, May 17, 2011

On setting Ranges in VBA

This post is probably not going to appeal to many of you, but it’s my blog and I shall do what I want with it.  And what I want right now is some help with programming in VBA.  Someone out there must know the answer…

So, I have a spreadsheet which looks like this:

image

I would like to write code which selects the data set, like this:

image

But as I don’t always know the size of the data set, I cannot use this code:

Range("A4:F21").Select

I have therefore attempted to write something like this:

With Range(“A4”)

.Range(.Offset(0, 0), .End(xlDown).End _(xlToRight)).Name = "DataSet"

End With

Range(“DataSet”).Select

Unfortunately, even though .Offset(0, 0).Address is A4, and .End(xlDown).End(xlToRight).Address is F21 my code actually selects this:

image

Why?

(Incidentally, the following alternative does not compile):

With Range(“A4”)

.Range(“.Offset(0, 0) : .End(xlDown).End _(xlToRight)”).Name = "DataSet"

End With

Range(“DataSet”).Select

2 comments:

Mark Porter said...

I'm not sure you have to do this in the VBA, I think you can do clever things with defined Ranges and Excel functions and then refer to the defined ranges in the VBA

JP... said...

Thanks, Mark (and to all the others who have in some way responded to my cry for help). Hope you're doing well?

The answer, it seems, lies in the fact that I should have used Range(.Offset ... rather than .Range(.Offset ...

As ever, it's all in the punctuation.