|
Find Last cell copy UP
I can't find a good explanation of how to do this in the discussions board. I
am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
To find the bottom cell in the last column.
Sub CopyLastCellinLastColumn() lric = Cells.Find(What:="*", After:=Cells(Rows.Count, Columns.Count), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Address Range(lric).Copy Range(lric).Offset(, -2) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Start here Pete
http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Thanks for trying Ron. I visited your site and man some of the stuff on there
is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Again; I want to find the very last active column and the the very last
active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
You understand this correctly. The second column to the left will also have
data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Try to run the macro "Test"
For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Ron, this code does find the last active cell range in my active sheet and
copies it to the first column to the left. What do I change to offset 2 columns to the left instead of one? After I copy and paste the data range, I need to first go back and delete the range I just copied. (Maybe Cutting and Pasteing would be better?) and then find the new last data range in the spreadsheet and "copy" - "Cut/Paste" it in the first available cell in the second column to the left. This code should repeat until only column "A" has data in it. -- Pete "Ron de Bruin" wrote: Try to run the macro "Test" For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Code looks like this
Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Columns(colnum).Copy Cells(1, colnum - 2) Columns(colnum).Delete End Sub You can add a loop -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, this code does find the last active cell range in my active sheet and copies it to the first column to the left. What do I change to offset 2 columns to the left instead of one? After I copy and paste the data range, I need to first go back and delete the range I just copied. (Maybe Cutting and Pasteing would be better?) and then find the new last data range in the spreadsheet and "copy" - "Cut/Paste" it in the first available cell in the second column to the left. This code should repeat until only column "A" has data in it. -- Pete "Ron de Bruin" wrote: Try to run the macro "Test" For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Ron, you have been a tremendous help on this. Your code works well, but I
have one minor hickup with it. Your code copies the last column as a whole and then replaces the second column to left with the copied info. I do not want to replace the existing info, I want to add to it. --------Example-------- "AA" "AC" 1 - 4 A 2 - 5 b 3 - 6 c 4 - 7 d Will turn into "AA" "AC" 1 - 4 2 - 5 3 - 6 4 - 7 5 - A 6 - b 7 - c 8 - d -- Pete "Ron de Bruin" wrote: Code looks like this Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Columns(colnum).Copy Cells(1, colnum - 2) Columns(colnum).Delete End Sub You can add a loop -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, this code does find the last active cell range in my active sheet and copies it to the first column to the left. What do I change to offset 2 columns to the left instead of one? After I copy and paste the data range, I need to first go back and delete the range I just copied. (Maybe Cutting and Pasteing would be better?) and then find the new last data range in the spreadsheet and "copy" - "Cut/Paste" it in the first available cell in the second column to the left. This code should repeat until only column "A" has data in it. -- Pete "Ron de Bruin" wrote: Try to run the macro "Test" For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
OK
This will work I think Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Do While colnum 2 Range(Cells(1, colnum), Cells(Last(1, Columns(colnum)), colnum)).Copy _ Cells(Last(1, Columns(colnum - 2)) + 1, colnum - 2) Columns(colnum).Delete colnum = Last(2, ActiveSheet.Cells) Loop End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, you have been a tremendous help on this. Your code works well, but I have one minor hickup with it. Your code copies the last column as a whole and then replaces the second column to left with the copied info. I do not want to replace the existing info, I want to add to it. --------Example-------- "AA" "AC" 1 - 4 A 2 - 5 b 3 - 6 c 4 - 7 d Will turn into "AA" "AC" 1 - 4 2 - 5 3 - 6 4 - 7 5 - A 6 - b 7 - c 8 - d -- Pete "Ron de Bruin" wrote: Code looks like this Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Columns(colnum).Copy Cells(1, colnum - 2) Columns(colnum).Delete End Sub You can add a loop -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, this code does find the last active cell range in my active sheet and copies it to the first column to the left. What do I change to offset 2 columns to the left instead of one? After I copy and paste the data range, I need to first go back and delete the range I just copied. (Maybe Cutting and Pasteing would be better?) and then find the new last data range in the spreadsheet and "copy" - "Cut/Paste" it in the first available cell in the second column to the left. This code should repeat until only column "A" has data in it. -- Pete "Ron de Bruin" wrote: Try to run the macro "Test" For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
Find Last cell copy UP
Ron; you are awesome......
-- Pete "Ron de Bruin" wrote: OK This will work I think Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Do While colnum 2 Range(Cells(1, colnum), Cells(Last(1, Columns(colnum)), colnum)).Copy _ Cells(Last(1, Columns(colnum - 2)) + 1, colnum - 2) Columns(colnum).Delete colnum = Last(2, ActiveSheet.Cells) Loop End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, you have been a tremendous help on this. Your code works well, but I have one minor hickup with it. Your code copies the last column as a whole and then replaces the second column to left with the copied info. I do not want to replace the existing info, I want to add to it. --------Example-------- "AA" "AC" 1 - 4 A 2 - 5 b 3 - 6 c 4 - 7 d Will turn into "AA" "AC" 1 - 4 2 - 5 3 - 6 4 - 7 5 - A 6 - b 7 - c 8 - d -- Pete "Ron de Bruin" wrote: Code looks like this Sub test() Dim colnum As Integer colnum = Last(2, ActiveSheet.Cells) Columns(colnum).Copy Cells(1, colnum - 2) Columns(colnum).Delete End Sub You can add a loop -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Ron, this code does find the last active cell range in my active sheet and copies it to the first column to the left. What do I change to offset 2 columns to the left instead of one? After I copy and paste the data range, I need to first go back and delete the range I just copied. (Maybe Cutting and Pasteing would be better?) and then find the new last data range in the spreadsheet and "copy" - "Cut/Paste" it in the first available cell in the second column to the left. This code should repeat until only column "A" has data in it. -- Pete "Ron de Bruin" wrote: Try to run the macro "Test" For example if column M is the last column with data it will copy column M to L You say that the columns are empty between the data columns so I past in the first cell If I am not correct let me know Sub test() Columns(Last(2, ActiveSheet.Cells)).Copy Cells(1, Last(2, ActiveSheet.Cells) - 1) End Sub Function Last(choice As Integer, rng As Range) 'Ron de Bruin, 20 Feb 2007 ' 1 = last row ' 2 = last column ' 3 = last cell Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 On Error Resume Next lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 On Error Resume Next Last = Cells(lrw, lcol).Address(False, False) If Err.Number 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 End Select End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... You understand this correctly. The second column to the left will also have data in it, so I will need to go to the end of the data range in the new column and paste what was previously copied in the first available cell. thanks -- Pete "Ron de Bruin" wrote: Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). If I understand you correct you want copy all cells in the last column with data on your worksheet and paste the data in the column before it ?? Let me know if I am correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... Thanks for trying Ron. I visited your site and man some of the stuff on there is awesome. Unfortunatly I could not figure out how to use the tips for my specific problem. Can you help further. Again; I want to find the very last active column and the the very last active cell and copy it (sometimes this is one cell and other times it is many). After copying the data range, I want to then paste whatever I copied in the first open cell in the column to the left. The columns are seperated by a empty column. thus the info is in every other coloumn starting with "A" and can go out as far as "FF" Thanks in advance -- Pete "Ron de Bruin" wrote: Start here Pete http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete" wrote in message ... I can't find a good explanation of how to do this in the discussions board. I am hoping someone can answer this quickly. I have speadsheets with lots of info in it. I want to find the very last active column and the the very last active cell and copy it. After copying the data range, I want to then past it in a the first open cell in the column to the left. My problem is the last active cell in any given sheet can be out as far as "CD55,000" or as close as "Z2". The next availible column is alway 2 columns to the left. (A, C, E, G.......) Any help is welcomed. -- Pete |
| All times are GMT +1. The time now is 10:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com