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

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

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

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

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

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

=offset(Sheet2!$A$1,0,0,CountA(Sheet2!$A:$A),1)

can be used to dynamically adjust to encompass all rows that contain data.

for example, in sheet1

=sum(offset(Sheet2!$A$1,0,2,CountA(Sheet2!$A:$A),1 ))

sums up column C of sheet2, using the entries in column A to establish the
number of rows. If column C is actually full of data, then you could use it
directly
=sum(offset(Sheet2!$C$1,0,0,CountA(Sheet2!$C:$C),1 ))

of course you can always do

=Sum(Sheet2!C:C)

or you can adjust the offset formula to start on a different row using the
2nd argument and subtract a constant from count

To sum from row 2 to the next to last row in column C
=sum(offset(Sheet2!$A$1,1,2,CountA(Sheet2!$A:$A)-2,1))



It sounds like you might want to use something like this.

--
Regards,
Tom Ogilvy

"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

Hi Tom,

I will try yours out later and let you know how I get on (got visitors -
hard to concentrate)

Many thanks
John

"Tom Ogilvy" wrote:

=offset(Sheet2!$A$1,0,0,CountA(Sheet2!$A:$A),1)

can be used to dynamically adjust to encompass all rows that contain data.

for example, in sheet1

=sum(offset(Sheet2!$A$1,0,2,CountA(Sheet2!$A:$A),1 ))

sums up column C of sheet2, using the entries in column A to establish the
number of rows. If column C is actually full of data, then you could use it
directly
=sum(offset(Sheet2!$C$1,0,0,CountA(Sheet2!$C:$C),1 ))

of course you can always do

=Sum(Sheet2!C:C)

or you can adjust the offset formula to start on a different row using the
2nd argument and subtract a constant from count

To sum from row 2 to the next to last row in column C
=sum(offset(Sheet2!$A$1,1,2,CountA(Sheet2!$A:$A)-2,1))



It sounds like you might want to use something like this.

--
Regards,
Tom Ogilvy

"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