Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

=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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

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

Adding Rows
 

Google it http://tinyurl.com/s4nfg

--
HTH

Bob Phillips

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

"JohnUK" wrote in message
...
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

Adding Rows
 

Thanks for that Bob

Regards
John

"Bob Phillips" wrote:

Google it http://tinyurl.com/s4nfg

--
HTH

Bob Phillips

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

"JohnUK" wrote in message
...
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