Selecting the first empty row

[webba]

How do I select the first empty row in a worksheet?

I'd like to copy a certain range form wksheet 1 and then past that range into the next available row in wksheet 2.

Thanks!
Weber

[Norman Jones]

Hi Webba,

Try something like:

Dim srcRng As Range
Dim destRng As Range

Set srcRng = Sheets("Sheet1").Range("A10:H15")

Set destRng = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy Destination:=destRng

---
Regards,
Norman



"webba" <[email protected]> wrote in
message news:[email protected]...
>
> How do I select the first empty row in a worksheet?
>
> I'd like to copy a certain range form wksheet 1 and then past that
> range into the next available row in wksheet 2.
>
> Thanks!
> Weber
>
>
> --
> webba
> ------------------------------------------------------------------------
> webba's Profile:
> http://www.excelforum.com/member.php...o&userid=26250
> View this thread: http://www.excelforum.com/showthread...hreadid=395463
>

[KC Cheung]

use Sheets("Sheet2").Range("A65536").End(xlUp) as destination will do

--
Regards
KC Cheung
The Inglewood

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Webba,
>
> Try something like:
>
> Dim srcRng As Range
> Dim destRng As Range
>
> Set srcRng = Sheets("Sheet1").Range("A10:H15")
>
> Set destRng = Sheets("Sheet2"). _
> Cells(Rows.Count, "A").End(xlUp)(2)
>
> srcRng.Copy Destination:=destRng
>
> ---
> Regards,
> Norman
>
>
>
> "webba" <[email protected]> wrote in
> message news:[email protected]...
> >
> > How do I select the first empty row in a worksheet?
> >
> > I'd like to copy a certain range form wksheet 1 and then past that
> > range into the next available row in wksheet 2.
> >
> > Thanks!
> > Weber
> >
> >
> > --
> > webba
> > ------------------------------------------------------------------------
> > webba's Profile:
> > http://www.excelforum.com/member.php...o&userid=26250
> > View this thread:
http://www.excelforum.com/showthread...hreadid=395463
> >
>
>

[Norman Jones]

Hi KC,

> use Sheets("Sheet2").Range("A65536").End(xlUp) as destination will do

Two points:

(1) Your suggestion returns the last populated cell in column A and would
result in some of the OP's data being overwritten.

(2) Suggesting the hardcoded 'Range("A65536").' rather than:

>> Cells(Rows.Count, "A").

does not strike me as an improvement. However, unless and untlil MS increase
worksheet specifications, it is equivalent.


---
Regards,
Norman



"KC Cheung" <[email protected]> wrote in message
news:[email protected]...
> use Sheets("Sheet2").Range("A65536").End(xlUp) as destination will do
>
> --
> Regards
> KC Cheung
> The Inglewood
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Webba,
>>
>> Try something like:
>>
>> Dim srcRng As Range
>> Dim destRng As Range
>>
>> Set srcRng = Sheets("Sheet1").Range("A10:H15")
>>
>> Set destRng = Sheets("Sheet2"). _
>> Cells(Rows.Count, "A").End(xlUp)(2)
>>
>> srcRng.Copy Destination:=destRng
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "webba" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > How do I select the first empty row in a worksheet?
>> >
>> > I'd like to copy a certain range form wksheet 1 and then past that
>> > range into the next available row in wksheet 2.
>> >
>> > Thanks!
>> > Weber
>> >
>> >
>> > --
>> > webba
>> > ------------------------------------------------------------------------
>> > webba's Profile:
>> > http://www.excelforum.com/member.php...o&userid=26250
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=395463
>> >
>>
>>
>
>

[webba]

This is great!
Thanks to both you guys!

[webba]

Would you know how to paste the range Transposed ?

Thanks!

[Norman Jones]

Hi Webba,

Try something like:

Dim srcRng As Range
Dim destRng As Range

Set srcRng = Sheets("Sheet1").Range("A10:A15")

Set destRng = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy
destRng.PasteSpecial , Transpose:=True


---
Regards,
Norman



"webba" <[email protected]> wrote in message
news:[email protected]...
>
> Would you know how to paste the range Transposed ?
>
> Thanks!
>
>
> --
> webba
> ------------------------------------------------------------------------
> webba's Profile:
> http://www.excelforum.com/member.php...o&userid=26250
> View this thread: http://www.excelforum.com/showthread...hreadid=395463
>

[webba]

That did the trick. Many thanks Norman!

[webba]

Hey guys!

Back again..

Now instead of choosing the first available row...

I'd like to choose the first available row BEFORE the total column all the way on the bottom.

Any help would be great.

Thanks!