Select last blank cell in a range of data

Hi,

During the running of my macro, I want the macro to be able to select the first blank cell it comes to, at the bottom of a list of data.

So, if cells A1:A12 contained data, then I'd want cell A13 to be selected by the macro.

Any ideas? Must be an easy one for some of you!

Try something like

Dim Rng As Range
Set Rng = Cells(Rows.Count, "A").End(xlUp)(2,1)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"DJ Dusty"
<[email protected]> wrote in
message
news:[email protected]...
>
> Hi,
>
> During the running of my macro, I want the macro to be able to
> select
> the first blank cell it comes to, at the bottom of a list of
> data.
>
> So, if cells A1:A12 contained data, then I'd want cell A13 to
> be
> selected by the macro.
>
> Any ideas? Must be an easy one for some of you!
>
>
> --
> DJ Dusty
> ------------------------------------------------------------------------
> DJ Dusty's Profile:
> http://www.excelforum.com/member.php...o&userid=16335
> View this thread:
> http://www.excelforum.com/showthread...hreadid=520077
>

Thanks, but hasn't worked - Do I use the Rng variable as a range selection?

To select the desired cell, try this:

Sub SelectFirstBlank()
Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select
end sub

HTH
--
AP

"DJ Dusty" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Thanks, but hasn't worked - Do I use the Rng variable as a range
> selection?
>
>
> --
> DJ Dusty
> ------------------------------------------------------------------------
> DJ Dusty's Profile:
http://www.excelforum.com/member.php...o&userid=16335
> View this thread: http://www.excelforum.com/showthread...hreadid=520077
>

This was exactly the code ive been looking for and ive used it to define where to paste some copied data into,

I would now like to manipulate it further so that I could copy the formula from row 2 all the way down to the last row, ive been trying something along the lines of

Range("F2").Select
Selection.Copy

Dim rng2 As Range
Set rng2 = Cells(Rows.Count, "A").End(xlUp)(1, 6)
rng2.Select

Range("F2:rng2").Select
ActiveSheet.Paste

It is not quite working,
1st question - can i use the bit in red?
2nd question - will this make all cells between F2 and rng2 active so values can be pasted into them?