Asp Forum - Find First empty cell at end of Column A - microsoft.public.excel.programming

DG

12/19/2006 5:38:00 PM

I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
will keep changing and I always want to append it to the bottom of sheet2.

Range("A1").End(xldown).Select will get me to the last USED cell. But how
do I go one more?

Also, I used Range(Range("A2").End(xlToRight),
Range("A2").End(xlDown)).Copy to copy the data. One I select the next unused
cell in sheet2 how do I paste?

Dan

7 Answers

Nick Hodge

12/19/2006 5:53:00 PM

Dan

Do your copying here.... then

Worksheets("Sheet2").Range("A1").End(XlDown).Offset(1,0).Select

Selection.Paste

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

"DG" <nothere@somewhere.com> wrote in message
news:OJMzpR5IHHA.4928@TK2MSFTNGP06.phx.gbl...
>I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
>will keep changing and I always want to append it to the bottom of sheet2.
>
> Range("A1").End(xldown).Select will get me to the last USED cell. But how
> do I go one more?
>
> Also, I used Range(Range("A2").End(xlToRight),
> Range("A2").End(xlDown)).Copy to copy the data. One I select the next
> unused cell in sheet2 how do I paste?
>
> Dan
>

Don Guillett

12/19/2006 5:57:00 PM

No need to SELECT.

Range("A1").End(xldown).offset(1)=1 'whateveryouwanthere
or this one liner
range("b2:b22")copy range("a1").end(xldown).offset(1)
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"DG" <nothere@somewhere.com> wrote in message
news:OJMzpR5IHHA.4928@TK2MSFTNGP06.phx.gbl...
>I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
>will keep changing and I always want to append it to the bottom of sheet2.
>
> Range("A1").End(xldown).Select will get me to the last USED cell. But how
> do I go one more?
>
> Also, I used Range(Range("A2").End(xlToRight),
> Range("A2").End(xlDown)).Copy to copy the data. One I select the next
> unused cell in sheet2 how do I paste?
>
> Dan
>

Dave Peterson

12/19/2006 6:02:00 PM

range("a1").end(xldown).offset(1,0).select

or maybe

with activesheet
.cells(.rows.count,"A").end(xlup).offset(1,0).select
end with

To come from the bottom up.

DG wrote:
>
> I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
> will keep changing and I always want to append it to the bottom of sheet2.
>
> Range("A1").End(xldown).Select will get me to the last USED cell. But how
> do I go one more?
>
> Also, I used Range(Range("A2").End(xlToRight),
> Range("A2").End(xlDown)).Copy to copy the data. One I select the next unused
> cell in sheet2 how do I paste?
>
> Dan

--

Dave Peterson

Dave Peterson

12/19/2006 6:02:00 PM

DG

12/19/2006 6:05:00 PM

Thanks,

That worked except the Selection.Paste didn't. However ActiveSheet.Paste
worked.

I only have one other problem. The paste brought over the formating
(borders) how do I use ActiveSheet.PasteSpecial to just past the values?

Dan

"Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
news:111FFFEB-488E-42A9-84A2-7941950495F2@microsoft.com...
> Dan
>
> Do your copying here.... then
>
> Worksheets("Sheet2").Range("A1").End(XlDown).Offset(1,0).Select
>
> Selection.Paste
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> www.nickhodge.co.uk
>
>
> "DG" <nothere@somewhere.com> wrote in message
> news:OJMzpR5IHHA.4928@TK2MSFTNGP06.phx.gbl...
>>I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
>>will keep changing and I always want to append it to the bottom of sheet2.
>>
>> Range("A1").End(xldown).Select will get me to the last USED cell. But
>> how do I go one more?
>>
>> Also, I used Range(Range("A2").End(xlToRight),
>> Range("A2").End(xlDown)).Copy to copy the data. One I select the next
>> unused cell in sheet2 how do I paste?
>>
>> Dan
>>
>

Nick Hodge

12/19/2006 6:12:00 PM

Dan

I'm not having a good day, activesheet was what I meant, as most have
pointed out this can also be done in a single operation (Pseudo code)

Range.Copy Destination:=Worksheets("Sheet2").Range("A1")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

"DG" <nothere@somewhere.com> wrote in message
news:eO0Gyg5IHHA.960@TK2MSFTNGP04.phx.gbl...
> Thanks,
>
> That worked except the Selection.Paste didn't. However ActiveSheet.Paste
> worked.
>
> I only have one other problem. The paste brought over the formating
> (borders) how do I use ActiveSheet.PasteSpecial to just past the values?
>
> Dan
>
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
> news:111FFFEB-488E-42A9-84A2-7941950495F2@microsoft.com...
>> Dan
>>
>> Do your copying here.... then
>>
>> Worksheets("Sheet2").Range("A1").End(XlDown).Offset(1,0).Select
>>
>> Selection.Paste
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
>> www.nickhodge.co.uk
>>
>>
>> "DG" <nothere@somewhere.com> wrote in message
>> news:OJMzpR5IHHA.4928@TK2MSFTNGP06.phx.gbl...
>>>I am copying a range of data from one sheet1 to the end of sheet2.
>>>Sheet1 will keep changing and I always want to append it to the bottom of
>>>sheet2.
>>>
>>> Range("A1").End(xldown).Select will get me to the last USED cell. But
>>> how do I go one more?
>>>
>>> Also, I used Range(Range("A2").End(xlToRight),
>>> Range("A2").End(xlDown)).Copy to copy the data. One I select the next
>>> unused cell in sheet2 how do I paste?
>>>
>>> Dan
>>>
>>
>
>

Don Guillett

12/19/2006 6:21:00 PM

To just get the values without formatting or pasting. NO copying and NO
pasting.

range("a2:a22").value=range("b2:b22").value

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"DG" <nothere@somewhere.com> wrote in message
news:eO0Gyg5IHHA.960@TK2MSFTNGP04.phx.gbl...
> Thanks,
>
> That worked except the Selection.Paste didn't. However ActiveSheet.Paste
> worked.
>
> I only have one other problem. The paste brought over the formating
> (borders) how do I use ActiveSheet.PasteSpecial to just past the values?
>
> Dan
>
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
> news:111FFFEB-488E-42A9-84A2-7941950495F2@microsoft.com...
>> Dan
>>
>> Do your copying here.... then
>>
>> Worksheets("Sheet2").Range("A1").End(XlDown).Offset(1,0).Select
>>
>> Selection.Paste
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
>> www.nickhodge.co.uk
>>
>>
>> "DG" <nothere@somewhere.com> wrote in message
>> news:OJMzpR5IHHA.4928@TK2MSFTNGP06.phx.gbl...
>>>I am copying a range of data from one sheet1 to the end of sheet2.
>>>Sheet1 will keep changing and I always want to append it to the bottom of
>>>sheet2.
>>>
>>> Range("A1").End(xldown).Select will get me to the last USED cell. But
>>> how do I go one more?
>>>
>>> Also, I used Range(Range("A2").End(xlToRight),
>>> Range("A2").End(xlDown)).Copy to copy the data. One I select the next
>>> unused cell in sheet2 how do I paste?
>>>
>>> Dan
>>>
>>
>
>

microsoft.public.excel.programming

Find First empty cell at end of Column A

DG

Nick Hodge

Don Guillett

Dave Peterson

Dave Peterson

DG

Nick Hodge

Don Guillett

x Login to ForumsZone