Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Snaking columns in Excel

1,094 views
Skip to first unread message

Mac

unread,
Feb 8, 2009, 2:43:01 PM2/8/09
to
I wish to prepare a book inventory. Column A is the book title, Column B is
the author. I will sort alphabetically by column B (author), then
alphabetically by column A (books will be in alphabetical order for that
author). I wish to have a total of four columns on each page, so will add
Columns C and D. I want the first two columns to SNAKE to the third and
fourth columns. Then, continue in that same format to page 2. Can you help?

Per Jessen

unread,
Feb 8, 2009, 3:20:11 PM2/8/09
to
Hi

Select columns A:D and goto Data > Sort > Set up the sort as desired.

Regards,
Per

"Mac" <M...@discussions.microsoft.com> skrev i meddelelsen
news:E7CDDC2F-1498-4CDE...@microsoft.com...

Gord Dibben

unread,
Feb 8, 2009, 4:42:34 PM2/8/09
to
After sorting the columns as you like.....................

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 2).Cut _
Destination:=Cells(iTarget, "C")

iSource = iSource + 100
iTarget = iTarget + 51
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 8 Feb 2009 11:43:01 -0800, Mac <M...@discussions.microsoft.com>
wrote:

AFSSkier

unread,
Mar 2, 2009, 1:57:03 PM3/2/09
to
Gord,

Your macro for snaking columns works great. However, I need the column
headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet.

Also have a different 2 column I need to snake to 8 columns (4 sets) in the
same manner. Each page is 54 rows at the page break.
--
Thanks, Kevin

Gord Dibben

unread,
Mar 2, 2009, 3:26:11 PM3/2/09
to
Revised.................you do the math if number of rows per set is not
correct

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 2
iTarget = 2
Range("A1:B1").Copy Range("C1:H1")
Do
Cells(iSource, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 54, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "C")
Cells(iSource + 108, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "E")
Cells(iSource + 162, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "G")
iSource = iSource + 216
iTarget = iTarget + 55 'insert a blank row



Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord

AFSSkier

unread,
Mar 2, 2009, 7:02:01 PM3/2/09
to
Assume, works great!

Thank you very much, Kevin

craigm

unread,
Nov 4, 2009, 3:36:18 PM11/4/09
to
how do you get rid of the blank row?

Gord Dibben wrote:

Revised.................
02-Mar-09

Revised.................you do the math if number of rows per set is not
correct

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 2
iTarget = 2
Range("A1:B1").Copy Range("C1:H1")
Do
Cells(iSource, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 54, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "C")
Cells(iSource + 108, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "E")
Cells(iSource + 162, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "G")
iSource = iSource + 216
iTarget = iTarget + 55 'insert a blank row

Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord


On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier
<AFSS...@discussions.microsoft.com> wrote:

Previous Posts In This Thread:

On Sunday, February 08, 2009 2:43 PM
Ma wrote:

Snaking columns in Excel


I wish to prepare a book inventory. Column A is the book title, Column B is
the author. I will sort alphabetically by column B (author), then
alphabetically by column A (books will be in alphabetical order for that
author). I wish to have a total of four columns on each page, so will add
Columns C and D. I want the first two columns to SNAKE to the third and
fourth columns. Then, continue in that same format to page 2. Can you help?

On Sunday, February 08, 2009 3:20 PM
Per Jessen wrote:

Re: Snaking columns in Excel
Hi

Select columns A:D and goto Data > Sort > Set up the sort as desired.

Regards,
Per

On Sunday, February 08, 2009 4:42 PM
Gord Dibben wrote:

After sorting the columns as you like.....................
After sorting the columns as you like.....................

Sub Move_Sets()


Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 2).Cut _
Destination:=Cells(iTarget, "C")


iSource = iSource + 100
iTarget = iTarget + 51

Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 8 Feb 2009 11:43:01 -0800, Mac <M...@discussions.microsoft.com>
wrote:

On Monday, March 02, 2009 1:57 PM
AFSSkie wrote:

Gord,Your macro for snaking columns works great.
Gord,

Your macro for snaking columns works great. However, I need the column
headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet.

Also have a different 2 column I need to snake to 8 columns (4 sets) in the
same manner. Each page is 54 rows at the page break.

--
Thanks, Kevin


"Gord Dibben" wrote:

On Monday, March 02, 2009 3:26 PM
Gord Dibben wrote:

Revised.................


Revised.................you do the math if number of rows per set is not
correct

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 2
iTarget = 2
Range("A1:B1").Copy Range("C1:H1")
Do
Cells(iSource, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 54, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "C")
Cells(iSource + 108, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "E")
Cells(iSource + 162, "A").Resize(54, 2).Cut _
Destination:=Cells(iTarget, "G")
iSource = iSource + 216
iTarget = iTarget + 55 'insert a blank row

Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord


On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier
<AFSS...@discussions.microsoft.com> wrote:

On Monday, March 02, 2009 7:02 PM
AFSSkie wrote:

Re: Snaking columns in Excel
Assume, works great!

Thank you very much, Kevin


"Gord Dibben" wrote:

EggHeadCafe - Software Developer Portal of Choice
Seamless Data Compression in .NET
http://www.eggheadcafe.com/tutorials/aspnet/a4733021-ed1c-4c3d-a60d-f4d9eaf13469/seamless-data-compression.aspx

Gord Dibben

unread,
Nov 4, 2009, 3:45:45 PM11/4/09
to
Before or after?

To not have a blank row inserted change this line

iTarget = iTarget + 55 'insert a blank row

iTarget = iTarget + 54

If you have already run the macro and have the blank lines...........

Select a column then F5>Special>Blanks>OK>Edit>Delete>Entire row.


Gord

ihock...@gmail.com

unread,
Mar 30, 2014, 11:36:49 PM3/30/14
to
This solution is fabulous. I changed a couple of things to make it 4 column for 7,300 rows and removed the blank row. Absolutely brilliant.
Ian

viz:
Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long
iSource = 2
iTarget = 2
Range("A1:D1").Copy Range("E1:H1")
Do
Cells(iSource, "A").Resize(54, 4).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 54, "A").Resize(54, 4).Cut _
Destination:=Cells(iTarget, "E")
iSource = iSource + 108
iTarget = iTarget + 54

0 new messages