How to find the first blank cell in a range

  • Thread starter Thread starter Victor
  • Start date Start date
V

Victor

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 said:
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.
Click to expand...

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 said:
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
Click to expand...

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 said:
This needs a little more thought; it doesn't work if D1 is blank or if
D1 is not but D2 is.

Alan Beban
Click to expand...


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
 
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
 
Alan Beban said:
If everything is happening on only one sheet

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

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
 
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
 
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
 
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
 
Hi Alan,

Thank you for responding! I understand your comments below.

Regards,
Kevin
 
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
 
GB said:
Hi Alan, I am afraid your code does not do what you said . . . it
simply finds the first blank cell within the range.
Click to expand...

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
Click to expand...

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
 
Back
Top