apologies for replying to my own post, but i should also have pointed out
that following on from what i said below, you need to assign a name to the
newly created dynamic range, then use that range name for whatever your
original purpose was!

hth,

tim

"Tim Marsh" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk wrote in message
...
unless you really want to use VBA, you could instead use a dynamic range,
based on column B. this will only work as long as there are no empty
cells in column B.

the following formula should be inserted into the 'refers to' box of the
'Define Name' form (Insert - Names - Define): -

=offset(Sheet1!$A$1,0,0,counta($B:$B),1)

this counts how many non-blank cells are in column b and selects that same
number of cells in column A (starting from cell A1). if you have header
rows that you do not want to include in the selection, you can
'counta($B:$B)-1' to remove 1 row from the total count.

hope that makes some kind of sense!

tim


"JohnUK" wrote in message
...
Hi,
If column B had data ranging from Row 1 to Row 100 say, I would then want
my
range in Column A to increase/decrease to match the number of rows Column
B
encompasses.

Can someone help with a VBA code to do this for me?

(The Range would consist of formulas that work off the data in Column B)

Many Thanks

John