Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging columns/rows
I have a spreadsheet of the form
NAME BOOK1 BOOK2 BOOK3... Bob aaa bbbb cccc Jill ddd eee Fred fff Jack ggg hhhh which I want to reformat into the form NAME BOOK Bob aaa Bob bbb Bob ccc Jill ddd Jill eee Fred fff etc Can anybody suggest the best way to do this? The no. of BOOKx columns will vary, and because the spreadsheet will be a regular one, I'll need a macro or such to automate the process TIA BernieH |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging columns/rows
Sub Test()
Dim iLastRow As Long Dim i As Long, j As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 For j = 3 To Cells(i, Columns.Count).End(xlToLeft).Column Rows(i + 1).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Cells(i, j).Value Cells(i, j).Value = "" Next j Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "BernieH" wrote in message ... I have a spreadsheet of the form NAME BOOK1 BOOK2 BOOK3... Bob aaa bbbb cccc Jill ddd eee Fred fff Jack ggg hhhh which I want to reformat into the form NAME BOOK Bob aaa Bob bbb Bob ccc Jill ddd Jill eee Fred fff etc Can anybody suggest the best way to do this? The no. of BOOKx columns will vary, and because the spreadsheet will be a regular one, I'll need a macro or such to automate the process TIA BernieH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I hide or unhide zero value columns/rows USING A MACRO? | Excel Discussion (Misc queries) | |||
How do I open or close grouped columns/rows in a protected excel s | Excel Discussion (Misc queries) | |||
Rearranging aTable | New Users to Excel | |||
columns/rows | New Users to Excel | |||
Grouped Columns/Rows | Excel Worksheet Functions |