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