How to move data in rows to columns
Solved/Closed
Related:
- How to move data in rows to columns
- Transfer data from one excel worksheet to another automatically - Guide
- Digital data transmission - Guide
- How to be offline in whatsapp when data is on - Guide
- How to download tiktok data after ban - Guide
- Excel data validation list from table multiple columns - Guide
1 reply
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
May 6, 2010 at 07:31 AM
May 6, 2010 at 07:31 AM
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
Feb 4, 2012 at 06:52 AM