Transpose Data in VBA

[tonybarr007]

This code was submitted as to how to paste into the first available row. If I wanted to transpose the paste and keep only the value and number format, where do I enter that into the code below?
(i.e.Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True)

Sub MIT()
Dim lRow As Long
' Find the FIRST EMPTY row by adding 1 to the last row
lRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1
'Paste the data into the first
'COMPLETELY empty row
ActiveSheet.Paste Destination:=Cells(lRow, 1)
End Sub

 

[bdmangum]

See modified code below. All you need to do is replace the paste command with the destination followed by the pastespecial command.

Sub MIT()
Dim lRow As Long
    ' Find the FIRST EMPTY row by adding 1 to the last row
    lRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row + 1
    'Paste the data into the first
    'COMPLETELY empty row
    [COLOR=red][b]ActiveSheet.Cells(lRow, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True[/b][/color]
End Sub

 

[SkipVought]

Tony,

This is the kind of question that should NEVER be asked.

If you turn on your macro recorder and do the transpose, you get the answer without asking the question.

If you then need help customizing the code, well, that's different, pardner!

Skip,

_[red][/red]

 

[bdmangum]

Skip,

He did use the recorder to grab the correct code, observe the section at the end of the first paragraph. Notice he asked where he is supposed to insert that code into the code below.

BD

 

[SkipVought]

My appologies, Tony!!!

Skip,

_[red][/red]

 

[tonybarr007]

Thanks bd that was exactly what I needed, and No worries Skip.

 

[cresbydotcom]

A minor point - I usually use the recorder out of laziness and it is a boon

usually.

Just once in a while it tells you WRONG.

More often you hit things it just cannot record.

Mostly it works. Thankyou Micro$oft (just a little).