I'm not sure which way you really want to go with:
1) one row with the initial entries along with 3 additional rows to hold the
added information in separate cells before beginning the next existing group,
or
2) one row for everything, with the combined description text in a single
cell on the same row.
So I have written up code that will let you decide for yourself.
The "IntoNewRows()" routine will take care of option 1, and the
"IntoOneCell()" routine takes care of the second option. The
"RemoveAddedRows()" routine can be used to delete the added rows inserted if
you use the 1st method.
To put the code into your workbook, open the workbook, press [Alt]+[F11] to
open the VB Editor. In the VB Editor use Insert --> Module and then copy the
code below and paste it into the code module presented to you. You can
modify things like the column width I set for column E and the number of
blanks in the various entries to try to get the layout out you want.
To run the code, go to the [Developer] tab and click the "Macros" icon and
choose the macro to run. If you do not see the [Developer] tab, use:
Office Button --> [Excel Options] and in the Popular group, choose to "Show
Developer tab in the Ribbon".
Sub IntoOneCell()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long
Dim theDescription As String
RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
theDescription = _
Range("A1") & String(14, " ") & anyCell & vbLf
theDescription = theDescription & _
Range("B1") & String(14, " ") & anyCell.Offset(0, 1) & vbLf
theDescription = theDescription & _
Range("C1") & String(2, " ") & anyCell.Offset(0, 2) & vbLf
theDescription = theDescription & _
Range("D1") & String(2, " ") & anyCell.Offset(0, 3)
anyCell.Offset(0, 4) = theDescription
End If
Next
Set initialRange = Nothing
End Sub
Sub IntoNewRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long
RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
Next
Set initialRange = Nothing
'get new range
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
anyCell.Offset(0, 4) = Range("A1") & String(9, " ") & anyCell
anyCell.Offset(1, 4) = Range("B1") & String(9, " ") &
anyCell.Offset(0, 1)
anyCell.Offset(2, 4) = Range("C1") & String(9, " ") &
anyCell.Offset(0, 2)
anyCell.Offset(3, 4) = Range("D1") & String(2, " ") &
anyCell.Offset(0, 3)
End If
Next
Set initialRange = Nothing
End Sub
Sub RemoveAddedRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
If IsEmpty(initialRange.Cells(LC, 1)) Then
initialRange.Cells(LC, 1).EntireRow.Delete
End If
Next
Set initialRange = Nothing
End Sub
urlocaljeweler said:
I am using Excel 2007.
I have 5 columns as illustrated below - (Table Description has no rows yet)
CTwt. Size Metal Jewelers cost Table Description
5 10 Tungsten 31
4 10.5 Tungsten 35
2 11 Tungsten 50
10 11.5 Tungsten 20
8 12 Tungsten 15
5 12.5 Tungsten 50
6 13 Tungsten 45
What I need to accomplish: Column 5(Table Description) needs to be a table
in each row. For example Column 5, Row 1: (new table with 2 columns and 4
rows) like this:
Ctwt. 5
Size 10
Metal Tungsten
Jewelers Cost 31
I need to do this with hundreds of rows. After this I will somehow convert
the table into html format and upload the excel file to my webpage....
Any ideas?
Thank you.