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

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
Tue May 17, 06:17:00 PM GMT
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.
Wed May 18, 09:44:00 AM GMT
Post a Comment

Popular posts from this blog

the purpose of religion

One of the things which featured in the recent debate on this post is the idea that “religion should be about improving oneself”. This has got me thinking; ‘religion’ of course takes many forms, but in the vast majority of cases I disagree with this. To paraphrase MarkC, religion is certainly something which as a by-product might help with “improving oneself”, but however you measure self-improvement it is not the sole point, certainly not in the Judeo-Christian case. In fact, if your sole aim is to “improve yourself”, why waste your time playing around with the trappings of religion? Most religions are based around the idea of faith in God, and that, surely, is the point. Considering Christianity specifically, Jesus said “ Love the Lord your God with all your heart and with all your soul and with all your mind. This is the first and greatest commandment. And the second is like it: Love your neighbor as yourself ” and so ‘religion’ should not be in danger of becoming all about wo...
Read more

atheism is a matter of faith, not science: the debate continues

Following the letters page in last Wednesday's Metro and the follow-up comment on this post I’m pleased to see that the beginnings of a debate are brewing, and I intend to use this post to pick up the baton again. Unsurprisingly, I’m going to start by responding to the aforementioned comment. I apologise if my tone was deemed to be a bit sharp, but I have no hesitation in defending the point I was trying to make. According to dictionary.com , faith can be defined as “belief that is not based on proof.” As there is no proof that God does not exist, belief that God does not exist (i.e. atheism) must be faith. Now, I take your point that “as human beings we are always revising and fine tuning what we hold to be true based on the evidence to hand, what we discover and our ability to determine what is probable or improbable.” To digress slightly, that is why switching on the Large Hadron Collider is going to be something of a milestone, as it could eit...
Read more

milk and sugar?

I've been debating with my housemate as to whether tea or coffee is more popular world-wide. We've both done quick web-searches, and we've both got results to match our different claims. I say tea, he says coffee. So, to settle the debate, it's down to you. Do you prefer tea or coffee? Please leave a comment below - one word is all it takes.
Read more