how to create a copy/paste special/transpose macro?

[gerry.lisa]

I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st
address in A1, A2-----2nd address B1, B2, etc)

Right now I am copying the 2 cells>>Paste Special>>Transpose

Is there a way to record a macro to do that?
Will that allow me to then click on the A1, then B1, then C1, etc and
run the macro using a single keystroke?

Or is there another better way?

I don't know VBA at all, though.

....Lisa

 

[gerry.lisa]

I know it's bad form to reply to your own messages but I got this far:

Sub transpose()
'
' transpose Macro
' Macro recorded 8/7/2007 by Lisa Hetherington
'
' Keyboard Shortcut: Ctrl+t
'
Range("A222:A223").Select
Selection.Copy
Range("B220").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, transpose:=True
End Sub


But I need the macro to work on whatever cell I click on.
Is that called a relative "something or other"?

....Lisa

 

[gerry.lisa]

I'm awful!!

The addresses are 2 rows each but are in A1, A2 then A3, A4 then A5,
A6

Thank you for anyyone who's reading this and is still willing to help
me out despite my confusing posts!!!!!

I want the macro to paste the contents in the B column, ie I want A1,
A2 to be transposed into B1 and C1

....Lisa

 

[JLatham]

Please check your addressing again. You say you have 650 addresses in 1300
rows. So I'm thinking
A1 = addr1 pt1
A2 = addr1 pt2
A3 = addr2 pt1
A4 = addr2 pt2
Which would use 1300 rows for 650 addresses. But you've tossed in column B
and C. I need a little better picture.

Maybe you have:
A B
1 addr1 pt1 addr326 pt1
2 addr1 pt2 addr326 pt2
3 addr2 pt1 addr327 pt1
4 addr2 pt2 addr327 pt2
.... on down to row 650?

and how do you want them to end up arranged? All in just column C? In
which case it would be a simple matter of copying and pasting the two lists
end to end.

 

[Gord Dibben]

In view of this latest info, this macro will give you two columns(A and B) of
transposed cells.


Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim J As Long

Set Rng = Cells(Rows.Count, 1).End(xlUp)
J = 1

For i = 1 To Rng.Row Step 2
Cells(J, "A").Resize(1, 2).Value = _
Application.Transpose(Cells(i, "A").Resize(2, 1))
J = J + 1
Next
End Sub


Gord Dibben MS Excel MVP

 

[gerry.lisa]

see my above correction
sorry for the confusion


....Lisa

 

[gerry.lisa]

Thanks!!

....Lisa

In view of this latest info, this macro will give you two columns(A and B) of
transposed cells.

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim J As Long

Set Rng = Cells(Rows.Count, 1).End(xlUp)
J = 1

For i = 1 To Rng.Row Step 2
Cells(J, "A").Resize(1, 2).Value = _
Application.Transpose(Cells(i, "A").Resize(2, 1))
J = J + 1
Next
End Sub

Gord Dibben MS Excel MVP