Hello,
How to transpose multiple data in last column (Please see attached) and remove blanks.
Thank you so much!
Transpose last column
-
- NewLounger
- Posts: 22
- Joined: 23 Nov 2018, 20:24
Transpose last column
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78474
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transpose last column
Here is a macro:
Code: Select all
Sub TransposeLast()
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim n As Long
Dim v1
Dim v2
Application.ScreenUpdating = False
m = Range("I" & Rows.Count).End(xlUp).Row
v1 = Range("A1:AZ" & m).Value
ReDim v2(1 To UBound(v1, 1), 1 To UBound(v1, 2))
t = 0
For s = 1 To m
If v1(s, 1) <> "" Then
t = t + 1
For c = 1 To 8
v2(t, c) = v1(t, c)
Next c
End If
If v1(s, 9) <> "" Then
v2(t, c) = v1(s, 9)
If c > n Then n = c
c = c + 1
End If
Next s
For c = 9 To n
v2(1, c) = "Office Name " & c - 8
Next c
Range("A1:AZ" & m).Value = v2
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 23 Nov 2018, 20:24
Re: Transpose last column
Thank you so much as always!!
-
- NewLounger
- Posts: 22
- Joined: 23 Nov 2018, 20:24
Re: Transpose last column
Hans -
The transpose is working great, but I have an error with the row alignment. Please see attached.
The transpose is working great, but I have an error with the row alignment. Please see attached.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78474
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transpose last column
Could you attach a sample workbook (without sensitive data) so that I can experiment? Thanks in advance.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 23 Nov 2018, 20:24
Re: Transpose last column
Please see attached.
Thank you!!
Thank you!!
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78474
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transpose last column
Thanks, that helped me see my stupid mistake.
Corrected version:
Corrected version:
Code: Select all
Sub TransposeLast()
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim n As Long
Dim v1
Dim v2
Application.ScreenUpdating = False
m = Range("I" & Rows.Count).End(xlUp).Row
v1 = Range("A1:AZ" & m).Value
ReDim v2(1 To UBound(v1, 1), 1 To UBound(v1, 2))
t = 0
For s = 1 To m
If v1(s, 1) <> "" Then
t = t + 1
For c = 1 To 8
v2(t, c) = v1(s, c)
Next c
End If
If v1(s, 9) <> "" Then
v2(t, c) = v1(s, 9)
If c > n Then n = c
c = c + 1
End If
Next s
For c = 9 To n
v2(1, c) = "Office Name " & c - 8
Next c
Range("A1:AZ" & m).Value = v2
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 23 Nov 2018, 20:24
Re: Transpose last column
Working as expected.
Much appreciated!!
Much appreciated!!
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Transpose last column
with Power Query
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Transpose last column
I'd prefer:
Code: Select all
Sub M_snb()
For Each it In Columns(1).SpecialCells(4).Areas
it.Offset(-1, 9).Resize(1, it.Rows.Count) = Application.Transpose(it.Offset(, 8))
Next
Columns(1).SpecialCells(4).EntireRow.Delete
End Sub