Excel Macro Problem

[New Judge]

I'm writing an Excel VBA macro anad my mind has gone blank.

I want to select a range. Normally this would be (for example):

Range("A1:D10").Select

What I want to do is to vary the range in accordance with the contents of another cell, E1, (where I have a "count" function).

So I want to select the range A1:D(the contents of E1). So if E1 contains the number 6 I want to select A1:D6.

The sort of thing I used to do every day but have lost the habit. Any help would be appreciated.Ta in advance.

[ABerrant]

Convert a text string into a cell reference using INDIRECT function

e.g. for D(the contents of E1) use =INDIRECT("D"&(E1))

and plug it into your RANGE thus

=RANGE(A1:INDIRECT("D"&(E1)))

[New Judge]

Thanks for the prompt reply, AB.

Unfortunately VBA does not like that at all!

In particular it does not seem to like the colon separator. I'll have a mess about with the syntax to see if I can make it work.

[New Judge]

Sorted it, AB! :-

ActiveSheet.Range(Cells(1, 1), Cells([e1], 4)).Select

This selects the range A1 to D(contents of cell E1). So if E1 contains '6' the range selected would be A1:D6

The Cell syntax in the brackets is Row, Column (so D3 for example would be 3, 4).

I’d used this in various orms many moons ago but it had completely slipped my mind. Thanks for your help.

[ABerrant]

Sorry for the bum steer - I was not aware the INDIRECT function was not supported in VBA.

Happy you got it sorted. It looks so simple when you switch to the R1C1 reference style.