I have a macro that copies and paste a range of values
into a bigger one. Of course the range has to be pasted to
the first available (blank) of the destination range.
Is there amacro that could helpme determine what that cell
is.
In the mean time I'm just positioning my cursor to the
cell I want the macro to paste to.

Thanks in advance for any help.

"Victor" wrote in message
...
I have a macro that copies and paste a range of values
into a bigger one. Of course the range has to be pasted to
the first available (blank) of the destination range.
Is there amacro that could helpme determine what that cell
is.
In the mean time I'm just positioning my cursor to the
cell I want the macro to paste to.

Thanks in advance for any help.


If D1 is the first cell in the destination range, then the following formula
will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

GB wrote:
If D1 is the first cell in the destination range, then the following formula
will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban

"Alan Beban" wrote in message
...
GB wrote:
If D1 is the first cell in the destination range, then the following
formula
will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban



The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).sele ct

If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:
"Alan Beban" wrote in message
...

GB wrote:

If D1 is the first cell in the destination range, then the following

formula

will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).sele ct

"Alan Beban" wrote in message
...
If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Hmm. Now I am really confused. I set up a destination range D1:F99. The
first 3 rows are full, except D2 is blank. Your code selected D2 rather than
D4. Am I going bonkers here?

Geoff



Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:
"Alan Beban" wrote in message
...

GB wrote:

If D1 is the first cell in the destination range, then the following

formula

will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).sele ct

Hi Alan,

Can you please explain your macro code? I am trying to increase my vba
proficiency.

Your code is as follows:


Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Assume that I have "destination" range that is A1:J25 (that is 25 rows, 10
cols).

I understand the first line where you assign destination to rng.

I am confused with the find statement "rng(rng.Rows.Count))".

rng.Rows.Count will be 25

rng(25) is the cell E3, the 25th cell in my "destination" range. I am not
sure how cell E3 has any bearing.

The syntax for the find statement is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte)

My understanding is that,

rng.Find("", rng(rng.Rows.Count)).Select
is now
rng.Find("", rng(E3)).Select

This means find a blank cell after the E3 cell, and then select it?

Why after E3?

Where did I go wrong in my understanding?

Regards,
Kevin






"Alan Beban" wrote in message
...
If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:
"Alan Beban" wrote in message
...

GB wrote:

If D1 is the first cell in the destination range, then the following

formula

will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).sele ct

Some confusion has arisen. My postings were responding to GB's below,
which dealt with a single-column destination range, which is what he
apparently assumed was meant by the original OP's post--not an
unreasonable assumption.

Now GB is working with a different spec--a multi-column range, and it is
no longer clear what's sought. For what it's worth, though, in the
specific example, what is selected is indeed the first blank cell in the
destination range, D2; not the first blank cell after the last non-empty
row, D4, which is what GB apparently intended by the changed spec.

Alan Beban

GB wrote:
"Alan Beban" wrote in message
...

If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select



Hmm. Now I am really confused. I set up a destination range D1:F99. The
first 3 rows are full, except D2 is blank. Your code selected D2 rather than
D4. Am I going bonkers here?

Geoff




Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:

"Alan Beban" wrote in message
...


GB wrote:


If D1 is the first cell in the destination range, then the following

formula


will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).s elect

Hi Kevin,

My code was in response to, and applicable to, GB's spec, which was to
select the first blank cell in a *single-column* destination range. For
Kevin Stecyk's different situation, a multi-column destination range, if
the first blank cell in the range is what is being sought, one can
change rng.Rows.Count to rng.Count (so long as the destination range is
a collection of cells and not a collection of rows or columns).

If one's looking to select something other than the first blank cell in
a multi-column destination range (see GB's most recent posting), that's
another spec again, with a different solution.

Kevin's understanding of the syntax of the Find method is correct; for a
single-column rng, rng.Rows.Count and rng.Count return the same value,
so either works to refer to the last cell in the range, which is what
needs to be referred to by the After parameter in order to find the
first blank cell in the range.

Alan Beban

Kevin Stecyk wrote:
Hi Alan,

Can you please explain your macro code? I am trying to increase my vba
proficiency.

Your code is as follows:


Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Assume that I have "destination" range that is A1:J25 (that is 25 rows, 10
cols).

I understand the first line where you assign destination to rng.

I am confused with the find statement "rng(rng.Rows.Count))".

rng.Rows.Count will be 25

rng(25) is the cell E3, the 25th cell in my "destination" range. I am not
sure how cell E3 has any bearing.

The syntax for the find statement is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte)

My understanding is that,

rng.Find("", rng(rng.Rows.Count)).Select
is now
rng.Find("", rng(E3)).Select

This means find a blank cell after the E3 cell, and then select it?

Why after E3?

Where did I go wrong in my understanding?

Regards,
Kevin






"Alan Beban" wrote in message
...

If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:

"Alan Beban" wrote in message
...


GB wrote:


If D1 is the first cell in the destination range, then the following

formula


will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).s elect

Hi Alan,

Thank you for responding! I understand your comments below.

Regards,
Kevin





"Alan Beban" wrote in message
...
Hi Kevin,

My code was in response to, and applicable to, GB's spec, which was to
select the first blank cell in a *single-column* destination range. For
Kevin Stecyk's different situation, a multi-column destination range, if
the first blank cell in the range is what is being sought, one can
change rng.Rows.Count to rng.Count (so long as the destination range is
a collection of cells and not a collection of rows or columns).

If one's looking to select something other than the first blank cell in
a multi-column destination range (see GB's most recent posting), that's
another spec again, with a different solution.

Kevin's understanding of the syntax of the Find method is correct; for a
single-column rng, rng.Rows.Count and rng.Count return the same value,
so either works to refer to the last cell in the range, which is what
needs to be referred to by the After parameter in order to find the
first blank cell in the range.

Alan Beban

Kevin Stecyk wrote:
Hi Alan,

Can you please explain your macro code? I am trying to increase my vba
proficiency.

Your code is as follows:


Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Assume that I have "destination" range that is A1:J25 (that is 25 rows,
10
cols).

I understand the first line where you assign destination to rng.

I am confused with the find statement "rng(rng.Rows.Count))".

rng.Rows.Count will be 25

rng(25) is the cell E3, the 25th cell in my "destination" range. I am
not
sure how cell E3 has any bearing.

The syntax for the find statement is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte)

My understanding is that,

rng.Find("", rng(rng.Rows.Count)).Select
is now
rng.Find("", rng(E3)).Select

This means find a blank cell after the E3 cell, and then select it?

Why after E3?

Where did I go wrong in my understanding?

Regards,
Kevin






"Alan Beban" wrote in message
...

If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:

"Alan Beban" wrote in message
...


GB wrote:


If D1 is the first cell in the destination range, then the following

formula


will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).s elect

Hi Alan, I am afraid your code does not do what you said.

Your code is:
Set rng = Range("destination")
rng.Find("", rng(rng.Count)).Select

In fact, your code has the same effect as
Set rng = Range("destination")
rng.Find("").Select

In other words, it simply finds the first blank cell within the range,
rather than the first blank row. The reason is that inserting rng(rng.Count)
in the code tells Find to search within the range but AFTER the last cell in
the range. You might expect Find not to work if instructed in this way.
Fortunately, Find loops back to the start of the range in these
circumstances and just looks for the first blank cell.

Geoff



"Alan Beban" wrote in message
...
Hi Kevin,

My code was in response to, and applicable to, GB's spec, which was to
select the first blank cell in a *single-column* destination range. For
Kevin Stecyk's different situation, a multi-column destination range, if
the first blank cell in the range is what is being sought, one can
change rng.Rows.Count to rng.Count (so long as the destination range is
a collection of cells and not a collection of rows or columns).

If one's looking to select something other than the first blank cell in
a multi-column destination range (see GB's most recent posting), that's
another spec again, with a different solution.

Kevin's understanding of the syntax of the Find method is correct; for a
single-column rng, rng.Rows.Count and rng.Count return the same value,
so either works to refer to the last cell in the range, which is what
needs to be referred to by the After parameter in order to find the
first blank cell in the range.

Alan Beban

Kevin Stecyk wrote:
Hi Alan,

Can you please explain your macro code? I am trying to increase my vba
proficiency.

Your code is as follows:


Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Assume that I have "destination" range that is A1:J25 (that is 25 rows,
10
cols).

I understand the first line where you assign destination to rng.

I am confused with the find statement "rng(rng.Rows.Count))".

rng.Rows.Count will be 25

rng(25) is the cell E3, the 25th cell in my "destination" range. I am
not
sure how cell E3 has any bearing.

The syntax for the find statement is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte)

My understanding is that,

rng.Find("", rng(rng.Rows.Count)).Select
is now
rng.Find("", rng(E3)).Select

This means find a blank cell after the E3 cell, and then select it?

Why after E3?

Where did I go wrong in my understanding?

Regards,
Kevin






"Alan Beban" wrote in message
...

If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:

"Alan Beban" wrote in message
...


GB wrote:


If D1 is the first cell in the destination range, then the following

formula


will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).select

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4).s elect

GB wrote:

Hi Alan, I am afraid your code does not do what you said . . . it
simply finds the first blank cell within the range.

That's what I said it does; see arrows below.

In fact, your code has the same effect as
Set rng = Range("destination")
rng.Find("").Select

No, it doesn't, as you will see if the first (but not the only) blank
cell in the destination range is the first cell in that range. In that
case my code

Set rng = Range("destination")
rng.Find("", rng(rng.Count)).Select

selects the first blank cell in the range after the last cell in the
range, giving effect to the wrapping of the Find method; i.e., it
selects the first blank cell in the range.

Set rng = Range("destination")
rng.Find("").Select

selects the first blank cell in the range after the first cell in the
range (which is the default After parameter); i.e., it selects the
second blank cell in the range.

Alan Beban



Your code is:
Set rng = Range("destination")
rng.Find("", rng(rng.Count)).Select

In fact, your code has the same effect as
Set rng = Range("destination")
rng.Find("").Select

In other words, it simply finds the first blank cell within the range,
rather than the first blank row. The reason is that inserting rng(rng.Count)
in the code tells Find to search within the range but AFTER the last cell in
the range. You might expect Find not to work if instructed in this way.
Fortunately, Find loops back to the start of the range in these
circumstances and just looks for the first blank cell.

Geoff



"Alan Beban" wrote in message
...

Hi Kevin,

My code was in response to, and applicable to, GB's spec, which was to
select the first blank cell in a *single-column* destination range. For
Kevin Stecyk's different situation, a multi-column destination range, if <-------
the first blank cell in the range is what is being sought, one can <-------
change rng.Rows.Count to rng.Count (so long as the destination range is
a collection of cells and not a collection of rows or columns).

If one's looking to select something other than the first blank cell in
a multi-column destination range (see GB's most recent posting), that's
another spec again, with a different solution.

Kevin's understanding of the syntax of the Find method is correct; for a
single-column rng, rng.Rows.Count and rng.Count return the same value,
so either works to refer to the last cell in the range, which is what
needs to be referred to by the After parameter in order to find the
first blank cell in the range.

Alan Beban

Kevin Stecyk wrote:

Hi Alan,

Can you please explain your macro code? I am trying to increase my vba
proficiency.

Your code is as follows:


Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select


Assume that I have "destination" range that is A1:J25 (that is 25 rows,

10

cols).

I understand the first line where you assign destination to rng.

I am confused with the find statement "rng(rng.Rows.Count))".

rng.Rows.Count will be 25

rng(25) is the cell E3, the 25th cell in my "destination" range. I am

not

sure how cell E3 has any bearing.

The syntax for the find statement is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder,

SearchDirection,

MatchCase, MatchByte)

My understanding is that,

rng.Find("", rng(rng.Rows.Count)).Select
is now
rng.Find("", rng(E3)).Select

This means find a blank cell after the E3 cell, and then select it?

Why after E3?

Where did I go wrong in my understanding?

Regards,
Kevin






"Alan Beban" wrote in message
...


If everything is happening on only one sheet

Set rng = Range("destination")
rng.Find("", rng(rng.Rows.Count)).Select

Otherwise

Set rng = Range("destination")
Sheets(rng.Parent.Name).Activate
rng.Find("", rng(rng.Rows.Count)).Select

Alan Beban

GB wrote:


"Alan Beban" wrote in message
...



GB wrote:



If D1 is the first cell in the destination range, then the following

formula



will select the first blank cell in the column below it.

Range("D1").End(xlDown).Offset(1,0).selec t

This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban




The OP indicated that he had a block of cells that he used for his
destination range. This implies contiguity, and it is possible to
over-complicate things.

Would the following be better in some circumstances?

Range ("D65536").End(xlUp).Offset(1,0).select

Cells(Range("D1").CurrentRegion.Rows.Count,4) .select