PDA

View Full Version : [SOLVED] VBA to stack top row into columns



elketa1252
05-19-2017, 02:46 PM
Hey guys I have a request. If I have the top row with data from "A1 to I1", "K1 to S1" and it goes like that until ZA1 or something like that, so they are groups of 9 cells together, how could I stack them all together from "A1 to I1" and then K1 to S1 would go to A2 to I2 and U1 to AC1 would go to A3 to I3 and so on. Any help would be appreciated.

Thanks.

Paul_Hossler
05-19-2017, 03:13 PM
something like that



Option Explicit

Sub Stack()
Dim i As Long, iLast As Long, iOut As Long

With ActiveSheet
iLast = .Cells(1, .Columns.Count).End(xlToLeft).Column

iOut = 2
For i = 11 To iLast Step 10
.Cells(1, i).Resize(1, 9).Copy .Cells(iOut, 1)
iOut = iOut + 1
Next i
End With
End Sub

elketa1252
05-19-2017, 04:23 PM
I don't know how it is so easy for you, I mean easy because it was not long ago I posted the request. Thanks a lot, you really saved me a lot of time.:clap::hi::rofl:

Paul_Hossler
05-19-2017, 05:36 PM
1. No Problem

2. It's usually the analysis that takes a while, the VBA part is usually easier so look over my macro and see what it's doing so that you can make your own changes if needed. Come back if there are any more questions

3. You can use [Thread Tools] above your first post to mark this [Solved]

elketa1252
05-19-2017, 06:13 PM
Thanks bud, the thread is marked as solved. Thanks a lot again and for now it doesn't look like I need to make any changes to it. Thanks

snb
05-20-2017, 03:25 AM
This code is sufficient (remove Option Explicit)


Sub M_snb()
For Each ar In Rows(1).SpecialCells(2).Areas
Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, ar.Columns.Count) = ar.Value
Next
End Sub

elketa1252
05-20-2017, 01:01 PM
Thanks!!!