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

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

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

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

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

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

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

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

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

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

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

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