I have a macro created for me that takes list with values with commas (see first list) and turns it into a list with new rows for the values the comma values while adding the values from the original row (see second list).
TI have two problems with the macro:
1) It overwrites the original list. I would prefer that it create an new tab named Output or that it ask me for a name.
2) It has to be a particular column. I would like to be able to tell it which column to use.
Sub Commas_to_Rows()
Dim rng As Range
Dim rngLotNos As Range
Dim arrLotNos
Set rng = Range("A2")
While rng.Value <> ""
arrLotNos = Split(rng.Offset(, 4).Value, ",")
If UBound(arrLotNos) > 0 Then
rng.Offset(1).Resize(UBound(arrLotNos)).EntireRow.Insert
rng.Resize(, 7).Copy rng.Resize(UBound(arrLotNos) + 1)
rng.Offset(, 4).Resize(UBound(arrLotNos) + 1) = Application.Transpose(arrLotNos)
End If
Set rng = rng.Offset(UBound(arrLotNos) + 1)
Wend
End Sub
<tbody>
</tbody>
and turns it into a list like this:
<tbody>
</tbody>
TI have two problems with the macro:
1) It overwrites the original list. I would prefer that it create an new tab named Output or that it ask me for a name.
2) It has to be a particular column. I would like to be able to tell it which column to use.
Sub Commas_to_Rows()
Dim rng As Range
Dim rngLotNos As Range
Dim arrLotNos
Set rng = Range("A2")
While rng.Value <> ""
arrLotNos = Split(rng.Offset(, 4).Value, ",")
If UBound(arrLotNos) > 0 Then
rng.Offset(1).Resize(UBound(arrLotNos)).EntireRow.Insert
rng.Resize(, 7).Copy rng.Resize(UBound(arrLotNos) + 1)
rng.Offset(, 4).Resize(UBound(arrLotNos) + 1) = Application.Transpose(arrLotNos)
End If
Set rng = rng.Offset(UBound(arrLotNos) + 1)
Wend
End Sub
| Order | President | Field 2 | Field 3 | Occupations | State |
| 1 | George Washington | Land Surveyor,Farmer,Military Officer | Virginia | ||
| 2 | John Adams | Lawyer,Farmer | Massachusetts | ||
| 3 | Thomas Jefferson | Land Surveyor,Writer,Inventor,Lawyer,Architect,Farmer,Diplomat,Linguist | Virginia | ||
| 4 | James Madison | Farmer | Virginia | ||
| 5 | James Monroe | Farmer,Lawyer | Virginia | ||
| 6 | John Quincy Adams | Lawyer | Massachusetts | ||
| 7 | Andrew Jackson | Military Officer,Lawyer | Tennessee |
<tbody>
</tbody>
and turns it into a list like this:
| Order | President | Field 2 | Field 3 | Occupations | State |
| 1 | George Washington | Land Surveyor | Virginia | ||
| 1 | George Washington | Farmer | Virginia | ||
| 1 | George Washington | Military Officer | Virginia | ||
| 2 | John Adams | Lawyer | Massachusetts | ||
| 2 | John Adams | Farmer | Massachusetts | ||
| 3 | Thomas Jefferson | Land Surveyor | Virginia | ||
| 3 | Thomas Jefferson | Writer | Virginia | ||
| 3 | Thomas Jefferson | Inventor | Virginia | ||
| 3 | Thomas Jefferson | Lawyer | Virginia | ||
| 3 | Thomas Jefferson | Architect | Virginia | ||
| 3 | Thomas Jefferson | Farmer | Virginia | ||
| 3 | Thomas Jefferson | Diplomat | Virginia | ||
| 3 | Thomas Jefferson | Linguist | Virginia | ||
| 4 | James Madison | Farmer | Virginia | ||
| 5 | James Monroe | Farmer | Virginia | ||
| 5 | James Monroe | Lawyer | Virginia | ||
| 6 | John Quincy Adams | Lawyer | Massachusetts | ||
| 7 | Andrew Jackson | Military Officer | Tennessee | ||
| 7 | Andrew Jackson | Lawyer | Tennessee |
<tbody>
</tbody>
