Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transpose Data in VBA

Status
Not open for further replies.

tonybarr007

Technical User
Jun 13, 2007
3
US
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
 
See modified code below. All you need to do is replace the paste command with the destination followed by the pastespecial command.

Code:
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
 



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,

[glasses] [red][/red]
[tongue]
 
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
 
Thanks bd that was exactly what I needed, and No worries Skip.
 
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).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top