Copy Selection - Paste Selection - Delete Selection

U

Uninvisible

I am trying to develop a macro which will allow me to copy a vertical
selection, paste special transpose it horizontally and then want to
delete (shift cells up) the vertical selection I copied. Any thoughts
on how to put this together? I am trying to develop one macro as I
need to copy and transpose several hundere records.
 
G

Guest

The code below should help. I assumed the first cell doesn't change. the
cells beneath the active cell gets moved to the right of the active cell. I
also assumed all the cells until the first blank cell will get moved. The
xlDown get all the cells until the first empty cell.


Sub remove()

Set firstcell = ActiveCell
firstcell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
firstcell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
firstcell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top