You guys are absolute geniuses
That worked great
Many thanks to both of you - Tom & Bob

Also one of my posts have disappeared regards adding a value depending on
info in another cell using VBA. Any idea if it can be retrieved because I
need it?

Again many thanks
Regards
John


"Tom Ogilvy" wrote:

Rows(iLastRow+1).Resize(NUM_ROWS).Insert


--
Regards,
Tom Ogilvy


"JohnUK" wrote in message
...
Hi Bob,

It worked great apart from it inserted the rows between the last line of
data leaving it at the bottom. Any ideas?
Regards
John

"Bob Phillips" wrote:

Try something like

Const NUM_ROWS As Long = 20 '<====== change to suit
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow).Resize(NUM_ROWS).Insert
' you now have a block of empty rows to do what you need with


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"JohnUK" wrote in message
...
Sorry - been away from the PC.
a) Before the new data comes over, the new rows are inserted between
the
existing data and the last row of the range, thus forcing the range to
grow
allowing my next code of the macro to copy/mend the formulas down
ready
for
the new data to be inserted.
Sorry not very good at explaining
John



"Bob Phillips" wrote:

John,

Just to make sure that I get this right. Are you saying that a) you
want
to
copy the formulae down to these new rows, or b) the existing
formulae
break
if you copy the new data in? If the latter, can you give an example?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing
direct)

"JohnUK" wrote in message
...
Hi Bob,
Because my data is within a range and my range has lots of
formulas
that
mend after some rows get added/deleted.
The idea is that I import some additional data from a different
workbook,
but depending on the amount of rows that data ocupies (lets say
20) I
then
want to increase by that amount within my range on the first
workbook
before
the data is entered.
I know I could just increase the range by x amount in the first
place
manually, but because there are so many formulas I am trying to
keep
the
size
of my workbook down.
John

"Bob Phillips" wrote:

There are already more than enough rows below 1000 for what you
want.
What
do you want to do with them?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing
direct)

"JohnUK" wrote in message
...
Hi,
Another problem I have:

Is there a way that rows can be added to the bottom of data
depending
on a
number given?
For example: If my data ended on row 1000 (The data
fluctuates)
and I
want
another 50 rows added (the amount of rows added would also
fluctuate)

Thanks
John