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