Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Sam, this will work to some extent if there is data below the target range,
it is also contiguous, and there is only one section of data. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. If there are several areas of data below the target data, then we will need a way to differentiate among them to find the range. Sub test() Dim LRow As Long Dim MyRng As Range Dim i As Long Dim ws As Worksheet For Each ws In Worksheets ws.Activate LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) i = MyRng.CurrentRegion.Rows.Count i = LRow - i If IsEmpty(Range("A" & i)) Then i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) Do While IsEmpty(MyRng) i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) If MyRng.Row = 1 Then LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) Exit Do End If Loop LRow = MyRng.Row Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1) End If Next Worksheets(1).Activate End Sub Mike F "Sam via OfficeKB.com" <u4102@uwe wrote in message news:77ea6409c2360@uwe... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Hi Mike,
Thank you for additional help. When I run Sub test(), I get run-time error '1004' on this line: If IsEmpty (Range("A" & i)) Then error message Method 'range' of object '_Global' failed. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. Could the above be specific sheets I list in an array, rather than all sheets? Please advise. Very much appreciated. Cheers, Sam Mike Fogleman wrote: Sam, this will work to some extent if there is data below the target range, it is also contiguous, and there is only one section of data. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. If there are several areas of data below the target data, then we will need a way to differentiate among them to find the range. Sub test() Dim LRow As Long Dim MyRng As Range Dim i As Long Dim ws As Worksheet For Each ws In Worksheets ws.Activate LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) i = MyRng.CurrentRegion.Rows.Count i = LRow - i If IsEmpty(Range("A" & i)) Then "ERROR returned" i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) Do While IsEmpty(MyRng) i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) If MyRng.Row = 1 Then LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) Exit Do End If Loop LRow = MyRng.Row Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1) End If Next Worksheets(1).Activate End Sub Mike F -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
I am not sure what dragon Mike is trying to slay - I didn't see it in your
initial description. I guess cells(rows.count,1).offset(1,1) being not empty, but then I am not sure why you would be copying data to that row. anyway Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) 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) Next sh End Sub -- Regards, Tom Ogilvy "Sam via OfficeKB.com" wrote: Hi Mike, Thank you for additional help. When I run Sub test(), I get run-time error '1004' on this line: If IsEmpty (Range("A" & i)) Then error message Method 'range' of object '_Global' failed. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. Could the above be specific sheets I list in an array, rather than all sheets? Please advise. Very much appreciated. Cheers, Sam Mike Fogleman wrote: Sam, this will work to some extent if there is data below the target range, it is also contiguous, and there is only one section of data. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. If there are several areas of data below the target data, then we will need a way to differentiate among them to find the range. Sub test() Dim LRow As Long Dim MyRng As Range Dim i As Long Dim ws As Worksheet For Each ws In Worksheets ws.Activate LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) i = MyRng.CurrentRegion.Rows.Count i = LRow - i If IsEmpty(Range("A" & i)) Then "ERROR returned" i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) Do While IsEmpty(MyRng) i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) If MyRng.Row = 1 Then LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) Exit Do End If Loop LRow = MyRng.Row Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1) End If Next Worksheets(1).Activate End Sub Mike F -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
After investing more time, I guess he means if there are multiple areas in
column A. Assuming the values are constants and not formulas, then this modification should handle that. Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = Columns(1).specialCells(xlConstants,xlNumbers).Are as(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub this assumes going down column A 1 or more blank cells a contiguous range of cells with number constants at least one blank cell (then there may be: other data in cells including blanks. ) If it is different from that, say how different. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: I am not sure what dragon Mike is trying to slay - I didn't see it in your initial description. I guess cells(rows.count,1).offset(1,1) being not empty, but then I am not sure why you would be copying data to that row. anyway Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) 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) Next sh End Sub -- Regards, Tom Ogilvy "Sam via OfficeKB.com" wrote: Hi Mike, Thank you for additional help. When I run Sub test(), I get run-time error '1004' on this line: If IsEmpty (Range("A" & i)) Then error message Method 'range' of object '_Global' failed. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. Could the above be specific sheets I list in an array, rather than all sheets? Please advise. Very much appreciated. Cheers, Sam Mike Fogleman wrote: Sam, this will work to some extent if there is data below the target range, it is also contiguous, and there is only one section of data. I have also modified the code to run on each sheet in the workbook, as you requested of Tom. If there are several areas of data below the target data, then we will need a way to differentiate among them to find the range. Sub test() Dim LRow As Long Dim MyRng As Range Dim i As Long Dim ws As Worksheet For Each ws In Worksheets ws.Activate LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) i = MyRng.CurrentRegion.Rows.Count i = LRow - i If IsEmpty(Range("A" & i)) Then "ERROR returned" i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) Do While IsEmpty(MyRng) i = i - 1 If i = 0 Then i = 1 Set MyRng = Range("A" & i) If MyRng.Row = 1 Then LRow = Cells(Rows.Count, 1).End(xlUp).Row Set MyRng = Range("A" & LRow) Exit Do End If Loop LRow = MyRng.Row Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1) End If Next Worksheets(1).Activate End Sub Mike F -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Hi Tom,
Thank you very much for further input. When I run Sub CopyLast(), it is adding two new rows of data to the first worksheet and no new row to the second worksheet specified. I have replied to your questions below. Cheers Sam Tom Ogilvy wrote: After investing more time, I guess he means if there are multiple areas in column A. Assuming the values are constants and not formulas, then this modification should handle that. Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = Columns(1).specialCells(xlConstants,xlNumbers).Are as(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub this assumes going down column A Yes 1 or more blank cells Yes, but before the start of contiguous range of cells and possibly after. a contiguous range of cells with number constants Yes at least one blank cell (then there may be: sometimes other data in cells including blanks. ) sometimes If it is different from that, say how different. If there is at least one blank cell below the contiguous range of cells, I would like that data excluded from the copy process; the last row should be qualified by the end of the contiguous data, anything after the contiguous data should be excluded. I am not sure what dragon Mike is trying to slay - I didn't see it in your initial description. I guess cells(rows.count,1).offset(1,1) being not [quoted text clipped - 78 lines] -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Add one more line after the For Each statement:
for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) sh.Activate Since the main code does not qualify which sheet the ranges, rows, columns, etc are on, you need to Activate each sheet in its' turn so the code will act upon the proper sheet. Mike F "Sam via OfficeKB.com" <u4102@uwe wrote in message news:77f689e239334@uwe... Hi Tom, Thank you very much for further input. When I run Sub CopyLast(), it is adding two new rows of data to the first worksheet and no new row to the second worksheet specified. I have replied to your questions below. Cheers Sam Tom Ogilvy wrote: After investing more time, I guess he means if there are multiple areas in column A. Assuming the values are constants and not formulas, then this modification should handle that. Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = Columns(1).specialCells(xlConstants,xlNumbers).Are as(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub this assumes going down column A Yes 1 or more blank cells Yes, but before the start of contiguous range of cells and possibly after. a contiguous range of cells with number constants Yes at least one blank cell (then there may be: sometimes other data in cells including blanks. ) sometimes If it is different from that, say how different. If there is at least one blank cell below the contiguous range of cells, I would like that data excluded from the copy process; the last row should be qualified by the end of the contiguous data, anything after the contiguous data should be excluded. I am not sure what dragon Mike is trying to slay - I didn't see it in your initial description. I guess cells(rows.count,1).offset(1,1) being not [quoted text clipped - 78 lines] -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Hi Mike,
That has done the trick! A huge Thank You to both you and Tom, for all your time and patience. Most appreciated. Cheers, Sam Mike Fogleman wrote: Add one more line after the For Each statement: for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) sh.Activate Since the main code does not qualify which sheet the ranges, rows, columns, etc are on, you need to Activate each sheet in its' turn so the code will act upon the proper sheet. Mike F -- Message posted via http://www.officekb.com |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Forgot to qualify Columns.
Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = sh.Columns(1).specialCells(xlConstants,xlNumbers). Areas(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub -- Regards, Tom Ogilvy "Sam via OfficeKB.com" wrote: Hi Tom, Thank you very much for further input. When I run Sub CopyLast(), it is adding two new rows of data to the first worksheet and no new row to the second worksheet specified. I have replied to your questions below. Cheers Sam Tom Ogilvy wrote: After investing more time, I guess he means if there are multiple areas in column A. Assuming the values are constants and not formulas, then this modification should handle that. Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = Columns(1).specialCells(xlConstants,xlNumbers).Are as(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub this assumes going down column A Yes 1 or more blank cells Yes, but before the start of contiguous range of cells and possibly after. a contiguous range of cells with number constants Yes at least one blank cell (then there may be: sometimes other data in cells including blanks. ) sometimes If it is different from that, say how different. If there is at least one blank cell below the contiguous range of cells, I would like that data excluded from the copy process; the last row should be qualified by the end of the contiguous data, anything after the contiguous data should be excluded. I am not sure what dragon Mike is trying to slay - I didn't see it in your initial description. I guess cells(rows.count,1).offset(1,1) being not [quoted text clipped - 78 lines] -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data
Hi Tom,
Thank you once again. Cheers, Sam Tom Ogilvy wrote: Forgot to qualify Columns. Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = sh.Columns(1).specialCells(xlConstants,xlNumbers). Areas(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200709/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How copy none excel data & paste in 2007 without overwriting data | Excel Discussion (Misc queries) | |||
Copy and paste - filtered data | Excel Discussion (Misc queries) | |||
Copy and Paste data | Excel Programming | |||
filted data, copy and paste a col. puts data in wrong row how fix | New Users to Excel | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |