Undoing Alt-Enter in a spread sheet

[bcoates]

I have a spreadsheet where one column is the customer name. The column next to it is the items the customer purchased. Normally the items column has a separate row for each item, but someone used the alt-enter method to put all the items in one cell instead of each item in its own row. Is there a way to undo this so each item is in its own row???

 

[xlbo]

there certainly should be but would need some input from you as to the criteria for what to do with tthe new rows...add to bottom or insert underneath current etc..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bcoates]

I would like to have the new rows to be inserted after the current.
for example:
what it is:
customer name item1
item2
item3
all of this is just one row since the items are in the same cell

i would like to have this
customer name item1
item2
item3
where it is a total of three cells because every item is in its own cell on its own row

 

[combo]

With some address validation:

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
    .EnableEvents = False
    If UBound(Split(Target.Text, Chr(10))) Then
        MsgBox "Please do not use ALT+ENTER key to break line." & vbCrLf & "Cell contents will be cleared.", vbCritical + vbOKOnly, "Wrong data format"
        .Undo
    End If
    .EnableEvents = True
End With
End Sub

combo

 

[bcoates]

Thanks combo but its too late for that the spreadsheet is already filled out with 32,000 rows in it. I need a retroactive solution

 

[combo]

So write the macro that checks UBound(Split(Target.Text, Chr(10))) in the range. (I missed test condition, should be If it is >0).
If it is >0, use output of split function to distribute it in proper cells.

combo

 

[bcoates]

I am not an advaced Excel VBA writer if you could show me what you mean???

 

[combo]

So you have a good reason to learn. See 'Split' and 'Ubound' functions in vba help file. There is a lot of code here that shows how to work with excel range. You can record some actions to get sample code (usually it has to be cleaned).
Up to excel 2003 the number of excel rows is limited to 64k, with 32k source rows you can have problems to finish in one sheet.
I would use a separate sheet to output the data.

combo

 

[WinblowsME]

If the total rows exceed Excel's row limit, you'll receive an error.

Sub Split_Items()
   Dim items As Variant, sheet_name As String
   Dim i As Long, j As Long, last_row As Long
   
   sheet_name = "Sheet1"   ' Sheet name where data resides
   Sheets(sheet_name).Activate
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   
   For i = last_row To 1 Step -1
      items = Split(Sheets(sheet_name).Cells(i, 2), Chr(10))
      Sheets(sheet_name).Rows(i + 1 & ":" & i + UBound(items)).Insert
      
      For j = 0 To UBound(items)
         Sheets(sheet_name).Cells(i + j, 2) = Trim(items(j))
      Next j
   Next i
End Sub