Related:
- How to move data in rows to columns
- A Macro to move data from rows to columns ✓ - Forum - Excel
- Moving data from rows into columns for every ✓ - Forum - Excel
- Converting rows of data in notepad to columns ✓ - Forum - Excel
- Excel data validation list multiple columns - How-To - Excel
- Excel - Rows and Columns RC - 7 - How-To - Excel
1 reply
rizvisa1
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
Assumptions
1. Data starts from Row 1 and is to be always copied to column A
2. Rows can be inserted without distortion to data
1. Data starts from Row 1 and is to be always copied to column A
2. Rows can be inserted without distortion to data
Sub TransposeSpecial()
Dim lMaxRows As Long 'max rows in the sheet
Dim lThisRow As Long 'row being processed
Dim iMaxCol As Integer 'max used column in the row being processed
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
lThisRow = 1 'start from row 1
Do While lThisRow < lMaxRows
iMaxCol = Cells(lThisRow, Columns.Count).End(xlToLeft).Column
If (iMaxCol > 1) Then
Rows(lThisRow + 1 & ":" & lThisRow + iMaxCol - 1).Insert
Range(Cells(lThisRow, 2), Cells(lThisRow, iMaxCol)).Copy
Range("A" & lThisRow + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range(Cells(lThisRow, 2), Cells(lThisRow, iMaxCol)).Clear
lThisRow = lThisRow + iMaxCol - 1
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
End If
lThisRow = lThisRow + 1
Loop
End Sub
Report
Ema
Thank you very much! Your program helped me a lot.
Subscribe To Our Newsletter!
