# **Description**
This macro takes the currently selected block of cells, copies all of its values, and writes them in a single transposed row starting one row above and one column to the right of the original top‑left cell. After writing the transposed values, it deletes the original selection and shifts the surrounding cells up, then selects the new transposed row for quick visual confirmation.
# Listing
```vb
Sub CopyTransposeAndDelete()
Dim src As Range
Dim dst As Range
Dim r As Long, c As Long
Dim idx As Long
Dim rCount As Long, cCount As Long
' Require a range selection
If TypeName(Selection) <> "Range" Then Exit Sub
Set src = Selection
rCount = src.Rows.Count
cCount = src.Columns.Count
' Destination: 1 row up, 1 column right from top‑left of selection
On Error Resume Next
Set dst = src.Cells(1, 1).Offset(-1, 1)
On Error GoTo 0
If dst Is Nothing Then Exit Sub ' e.g. selection starts in row 1
' Ensure the destination row has enough columns
If dst.Column + (rCount * cCount) - 1 > src.Parent.Columns.Count Then Exit Sub
' Write all values from src into a single row, left‑to‑right
idx = 0
For r = 1 To rCount
For c = 1 To cCount
idx = idx + 1
dst.Offset(0, idx - 1).Value = src.Cells(r, c).Value
Next c
Next r
' Delete original cells and shift up
src.Delete Shift:=xlUp
' Reselect the new transposed row
dst.Resize(1, rCount * cCount).Select
End Sub
```
# Installation
To install and use this macro in Excel:
## Add the macro to a workbook
1. Open the workbook where you want this macro available.
2. Press **Alt + F11** to open the Visual Basic Editor (VBE).[](https://exceltip2day.blogspot.com/2013/04/alt-f11-excel-shortcut.html)
3. In the left “Project – VBAProject” pane, right‑click your workbook (e.g., `VBAProject (Book1.xlsm)`) and choose **Insert → Module**.[](https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/)
4. In the new module window, paste the code (***above***)
5. Save the workbook as **Excel Macro‑Enabled Workbook (*.xlsm)** via **File → Save As**.[](https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/)
## Enable macros (if needed)
1. If you see a **Security Warning: Macros have been disabled** bar when opening the file, click **Enable Content**.[](https://www.geeksforgeeks.org/excel/how-to-insert-and-run-vba-code-in-excel/)
2. For persistent use, you can adjust macro settings under **File → Options → Trust Center → Trust Center Settings → Macro Settings** (typically “Disable all macros with notification” is a safe choice).[](https://www.geeksforgeeks.org/excel/how-to-insert-and-run-vba-code-in-excel/)
## Run the macro
- Select the range you want to convert and delete.
- Press **Alt + F8**, choose `CopyTransposeAndDelete`, and click **Run**.[](https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/)
## (Optional) Assign a keyboard shortcut
1. Go to the **Developer** tab → **Macros** (or press Alt + F8).[](https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/)
2. Select `CopyTransposeAndDelete` and click **Options…**.
3. Set a shortcut (for example, `Ctrl + Shift + T`) and click **OK**.[](https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/)
---
You assign a shortcut in Excel through the Macros dialog; the shortcut itself ends up as `Ctrl+letter` or `Ctrl+Shift+letter`.
## Assign a shortcut to `CopyTransposeAndDelete`
1. Make sure your macro is in the workbook and saved as `.xlsm`.
2. In Excel, press **Alt + F8** to open the **Macro** dialog.[](https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f)
3. In the list, click `CopyTransposeAndDelete` (or whatever you named it).[](https://support.microsoft.com/en-us/office/run-a-macro-in-excel-5e855fd2-02d1-45f5-90a3-50e645fe3155)
4. Click the **Options…** button.
5. In **Shortcut key**, type:
- a lowercase letter (e.g. `t`) for **Ctrl + t**, or
- an uppercase letter (e.g. `T`) for **Ctrl + Shift + T**.
6. Optionally add a brief description in the Description box.[](https://bettersolutions.com/vba/macros/running-shortcut-keys.htm)
7. Click **OK**, then **Close** on the Macro dialog.
Now, with any workbook where that macro is available and macros are enabled, you can:
- Select your source range.
- Press your chosen shortcut (e.g. **Ctrl + Shift + T**) to run the macro.[](https://www.launchexcel.com/assign-shortcut-key-excel-macro)
---