Hi Tim
I wasnt to sure at first because I couldnt see the Range on the sheet (by
minimising the view to 25%, nor from the window dropdown) but after I entered
the code into my VBA project it worked a treat.
This is how the code looks:
(I called the Range €œRange€�)

Range("A2").Select
ActiveCell.FormulaR1C1 =
RC[2]&IF(Report!R2C2=""All"",""All"",RC[6])&RC[14]&IF(Report!R3C2=""All"",""All"",RC[3])"
Selection.Copy
Application.Goto Reference:="Range"
Selection.PasteSpecial Paste:=xlPasteFormulas

Many thanks Tim

Regards
John

"Tim Marsh" wrote:

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