Hi All,

I would like to:

1) Find the LAST row of consecutive data (starts at column "A" - numeric
values in col "A", start row number varies)

2) Selct the FIRST to LAST cell of continuous data on that LAST row

3) Copy the FIRST to LAST cell of continuous data on that LAST row

4) Paste the copied data in the same cell position on the row immediately
below

If possible, can a solution be provided to do the above process on multiple
worksheets within the same workbook?

Thanks,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1

If A13 and B13 have values, C13 is empty, and D13 has a value,
then
do you want A13 thru B13 or A13 thru D13?
--
Gary''s Student - gsnu200743


"Sam via OfficeKB.com" wrote:

> Hi All,
>
> I would like to:
>
> 1) Find the LAST row of consecutive data (starts at column "A" - numeric
> values in col "A", start row number varies)
>
> 2) Selct the FIRST to LAST cell of continuous data on that LAST row
>
> 3) Copy the FIRST to LAST cell of continuous data on that LAST row
>
> 4) Paste the copied data in the same cell position on the row immediately
> below
>
> If possible, can a solution be provided to do the above process on multiple
> worksheets within the same workbook?
>
> Thanks,
> Sam
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200709/1
>
>

Hi Gary''s Student,

Thank you for reply.

I would like A13 thru B13

Cheers,
Sam

Gary''s Student wrote:
>If A13 and B13 have values, C13 is empty, and D13 has a value,
>then
>do you want A13 thru B13 or A13 thru D13?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1

Is there other data surrounding this contiguous data that we need to avoid?
If not, then this simple code will do it:

Sub test()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(LRow).Copy Rows(LRow + 1)
End Sub

Mike F
"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:77e91b9497994@uwe...
> Hi All,
>
> I would like to:
>
> 1) Find the LAST row of consecutive data (starts at column "A" - numeric
> values in col "A", start row number varies)
>
> 2) Selct the FIRST to LAST cell of continuous data on that LAST row
>
> 3) Copy the FIRST to LAST cell of continuous data on that LAST row
>
> 4) Paste the copied data in the same cell position on the row immediately
> below
>
> If possible, can a solution be provided to do the above process on
> multiple
> worksheets within the same workbook?
>
> Thanks,
> Sam
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200709/1
>

Hi Mike,

Thanks for reply and assistance.

Does your solution take into account Gary's Student Post:

>If A13 and B13 have values, C13 is empty, and D13 has a value,
>then
>do you want A13 thru B13 or A13 thru D13?

I would like A13 thru B13

>Mike Fogleman wrote:
>Is there other data surrounding this contiguous data that we need to avoid?
>If not, then this simple code will do it:

Sub test()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(LRow).Copy Rows(LRow + 1)
End Sub

>Mike F

Mike, will this code work with my request for A13 thru B13 based on above,
selecting only contiguous data filled cells on that row?

Please advise.

Cheers,
Sam

--
Message posted via http://www.officekb.com

Sub CopyLast()
Dim r1 as Range, r2 as Range
set r1 = cells(rows.count,1).End(xlup)
if isempty(r1(1,2)) then
set r2 = r1
else
set r2 = r1.end(xltoRight)
end if
Range(r1,r2).Copy r1(2)
End Sub

--
Regards,
Tom Ogilvy


"Sam via OfficeKB.com" wrote:

> Hi Mike,
>
> Thanks for reply and assistance.
>
> Does your solution take into account Gary's Student Post:
>
> >If A13 and B13 have values, C13 is empty, and D13 has a value,
> >then
> >do you want A13 thru B13 or A13 thru D13?
>
> I would like A13 thru B13
>
> >Mike Fogleman wrote:
> >Is there other data surrounding this contiguous data that we need to avoid?
> >If not, then this simple code will do it:
>
> Sub test()
> Dim LRow As Long
>
> LRow = Cells(Rows.Count, 1).End(xlUp).Row
> Rows(LRow).Copy Rows(LRow + 1)
> End Sub
>
> >Mike F
>
> Mike, will this code work with my request for A13 thru B13 based on above,
> selecting only contiguous data filled cells on that row?
>
> Please advise.
>
> Cheers,
> Sam
>
> --
> Message posted via http://www.officekb.com
>
>

This one does exclude any data on the last row that is to the right of a
blank cell. However, if there is data further down in column A after some
blank cells then this will find it and copy that row. More code would be
needed to avoid data that is below the contiguous data in column A.

Sub test()
Dim LRow As Long
Dim MyRng As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRng = Range("A" & LRow)
Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1)
End Sub

Mike F
"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:77e9d5b8918b2@uwe...
> Hi Mike,
>
> Thanks for reply and assistance.
>
> Does your solution take into account Gary's Student Post:
>
>>If A13 and B13 have values, C13 is empty, and D13 has a value,
>>then
>>do you want A13 thru B13 or A13 thru D13?
>
> I would like A13 thru B13
>
>>Mike Fogleman wrote:
>>Is there other data surrounding this contiguous data that we need to
>>avoid?
>>If not, then this simple code will do it:
>
> Sub test()
> Dim LRow As Long
>
> LRow = Cells(Rows.Count, 1).End(xlUp).Row
> Rows(LRow).Copy Rows(LRow + 1)
> End Sub
>
>>Mike F
>
> Mike, will this code work with my request for A13 thru B13 based on
> above,
> selecting only contiguous data filled cells on that row?
>
> Please advise.
>
> Cheers,
> Sam
>
> --
> Message posted via http://www.officekb.com
>

Hi Tom, neither of our code addresses the situation if there is
non-contiguous data further down in column A. Awaiting the op's answer on
that.

Mike F
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:0A1B07EF-BA1A-4172-8811-(E-Mail Removed)...
> Sub CopyLast()
> Dim r1 as Range, r2 as Range
> set r1 = cells(rows.count,1).End(xlup)
> if isempty(r1(1,2)) then
> set r2 = r1
> else
> set r2 = r1.end(xltoRight)
> end if
> Range(r1,r2).Copy r1(2)
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Sam via OfficeKB.com" wrote:
>
>> Hi Mike,
>>
>> Thanks for reply and assistance.
>>
>> Does your solution take into account Gary's Student Post:
>>
>> >If A13 and B13 have values, C13 is empty, and D13 has a value,
>> >then
>> >do you want A13 thru B13 or A13 thru D13?
>>
>> I would like A13 thru B13
>>
>> >Mike Fogleman wrote:
>> >Is there other data surrounding this contiguous data that we need to
>> >avoid?
>> >If not, then this simple code will do it:
>>
>> Sub test()
>> Dim LRow As Long
>>
>> LRow = Cells(Rows.Count, 1).End(xlUp).Row
>> Rows(LRow).Copy Rows(LRow + 1)
>> End Sub
>>
>> >Mike F
>>
>> Mike, will this code work with my request for A13 thru B13 based on
>> above,
>> selecting only contiguous data filled cells on that row?
>>
>> Please advise.
>>
>> Cheers,
>> Sam
>>
>> --
>> Message posted via http://www.officekb.com
>>
>>

Hi Tom,

Thank you very much for reply and assistance.

Your code does the job Brilliantly!

However, could you adapt it to perform this routine on multiple worksheets
within the same workbook.

Further assistance very much appreciated.

Cheers
Sam

>Tom Ogilvy wrote:
Sub CopyLast()
Dim r1 as Range, r2 as Range
set r1 = cells(rows.count,1).End(xlup)
if isempty(r1(1,2)) then
set r2 = r1
else
set r2 = r1.end(xltoRight)
end if
Range(r1,r2).Copy r1(2)
End Sub

--
Message posted via http://www.officekb.com

Hi Mike,

Thank you very much indeed for your time and assistance.

Your code works Great!

>However, if there is data further down in column A after some
>blank cells then this will find it and copy that row. More code would be
>needed to avoid data that is below the contiguous data in column A.

Would it be possible for you to extend your Sub test() routine and provide
the additional code that would take the above scenario into account.

Much appreciated.

Cheers,
Sam

Mike Fogleman wrote:
>This one does exclude any data on the last row that is to the right of a
>blank cell. However, if there is data further down in column A after some
>blank cells then this will find it and copy that row. More code would be
>needed to avoid data that is below the contiguous data in column A.

>Sub test()
>Dim LRow As Long
>Dim MyRng As Range

>LRow = Cells(Rows.Count, 1).End(xlUp).Row
>Set MyRng = Range("A" & LRow)
>Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1)
>End Sub

>Mike F

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1