Transpose VBA Excel
Closed
plexus2121
Posts
2
Registration date
Monday February 4, 2019
Status
Member
Last seen
February 6, 2019
-
Feb 4, 2019 at 09:11 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 11, 2019 at 12:30 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 11, 2019 at 12:30 PM
Related:
- Vba transpose
- Vba case like - Guide
- Number to words in excel formula without vba - Guide
- Vba check if value is in array - Guide
- Vba color index - Guide
- How to open vba in excel mac - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 5, 2019 at 12:11 PM
Feb 5, 2019 at 12:11 PM
Hi Plexus,
The following code will work for your sample data:
Then I read you had 16 channels ...
Can you adjust the code to make that happen?
Best regards,
Trowa
The following code will work for your sample data:
Sub RunMe() Dim lRow, x As Integer Sheets("Sheet1").Select lRow = Range("A1").End(xlDown).Row For Each cell In Range("A2:A" & lRow) If cell.Offset(0, 3).Value = vbNullString Then x = 1 ElseIf cell.Offset(0, 4).Value = vbNullString Then x = 2 ElseIf cell.Offset(0, 5).Value = vbNullString Then x = 3 Else x = 4 End If Range(cell, cell.Offset(0, 1)).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(x).PasteSpecial Range(cell.Offset(0, 6), cell.Offset(0, 8)).Copy Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Resize(x).PasteSpecial cell.Offset(0, 2).Resize(1, x).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True Next cell Application.CutCopyMode = False End Sub
Then I read you had 16 channels ...
Can you adjust the code to make that happen?
Best regards,
Trowa
Feb 6, 2019 at 09:17 AM
Thank you so much for your help. I have edited the code to work on 16 channels. I also added some paste special values. One thing that I am unable to solve is the code does not handle empty cells in channels when pasting into sheet 2. It pastes down incorrectly then gets out of sync. Here is what I have:
I was confused as to what this step is doing and was wondering if this is causing the problem?
Once again thanks for your help!
Plex.
Feb 11, 2019 at 12:30 PM
You were almost there, you just overlooked something.
The part where x is determined, the offset value for the column should increase by 1. In the amended code it goes from 3, 4, 5, 5, 5 .... instead of 3, 4, 5, 6, 7 ....
Take the cell from Range("A2:A" & lRow), then 2 cells to the right (offest(0,2) and then resize that cells to the number of channels that have values in them.
Let me know if something is still unclear.
Best regards,
Trowa