Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting a table
I have a spreadsheet with songtitles associated to a CD catlaogue numbe
that is laid out like this: [image: http://www.firstfoot.com/Library/images/excelsheet.jpg] To get it into a format to impert it into a database, I need to make i look like this: [image: http://www.firstfoot.com/Library/ima...celsheet2.jpg] Where the song title is horizontally aligned with each catalogu number. But I just can't suss the macro to do it. My head is sore from banging it on the screen. Any ideas -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting a table
Here is one way
Sub CDCollection() Dim cLastRow As Long Dim rng As Range Dim i As Long Columns("A:A").Cut Range("C1").Insert Shift:=xlToRight cLastRow = Cells(Rows.Count, "A").End(xlUp).row For i = cLastRow To 2 Step -1 If Cells(i, "A").Value = Cells(i - 1, "A").Value Then Cells(i, "A").Offset(0, 1).Resize(1, 20).Copy _ Destination:=Cells(i - 1, "C") If rng Is Nothing Then Set rng = Cells(i, "A") Else Set rng = Union(rng, Cells(i, "A")) End If End If Next i If Not rng Is Nothing Then rng.EntireRow.Delete End If Range("A1").Select End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "firstfoot " wrote in message ... I have a spreadsheet with songtitles associated to a CD catlaogue number that is laid out like this: [image: http://www.firstfoot.com/Library/images/excelsheet.jpg] To get it into a format to impert it into a database, I need to make it look like this: [image: http://www.firstfoot.com/Library/ima...celsheet2.jpg] Where the song title is horizontally aligned with each catalogue number. But I just can't suss the macro to do it. My head is sore from banging it on the screen. Any ideas? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting a table
Bob
Many thanks for that. It worked a treat and has saved me from inflicting more pain on m already severely bruised head: -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformatting | Excel Discussion (Misc queries) | |||
Help With Reformatting | Excel Discussion (Misc queries) | |||
Stopping a pivot table from reformatting column sizes and widths | Excel Discussion (Misc queries) | |||
reformatting dates | Excel Discussion (Misc queries) | |||
REformatting PivotChart | Excel Programming |