-2

See this example below. I really just don't know how to programmatically enter the "alt+enter" for a cell string using the "cells()" function.

Before:

enter image description here

After:

enter image description here

Here's the requested attempt at the code:

Sub Button2_Click()

Dim lenrows
lenrows = Cells(Rows.Count, "A").End(xlUp).Row 'length of column 1 (from the start)

For i = 2 To lenrows

    If Cells(i, 1) = "" Then ' for example the cell to the left of banana, cells(1,2) is techincally blank (not 100)
        
        Cells(i - 1, 2) = Cells(i - 1, 2) & " " & Cells(i, 2) 'instead of " ", it would need to be something that enters "alt+enter"
        
        Rows(i).EntireRow.Delete
        
        If i = Cells(Rows.Count, "A").End(xlUp).Row Then 'next line "i = i - 1" would make it loop forever?
            Exit Sub
        End If
        
        i = i - 1 'otherwise turtle, for example, wouldn't be added

    End If

Next i

End Sub

When I run this, it actually runs indefinitely and crashes. Not sure what to enter to stop this. I think it has to do with the "i = i - 1", but if I don't add that line, it wouldn't add turtle with pear and potato

After incorporating comments, here's the final working code:

Sub Button4_Click()


Dim lenrows
lenrows = Cells(Rows.Count, "B").End(xlUp).Row 'length of column 1 (from the start)

For i = lenrows To 2 Step -1

    If Cells(i, 1) = "" Then ' for example the cell to the left of banana, cells(1,2) is techincally blank (not 100)
        
        Cells(i - 1, 2) = Cells(i - 1, 2) & Chr(10) & Cells(i, 2)
        
        Rows(i).EntireRow.Delete
        
    End If

Next i

    
End Sub
Bijan Sanchez
  • 39
  • 5
  • Specifically [this answer](https://stackoverflow.com/a/9902628) – chris neilsen Jun 20 '22 at 19:08
  • My apologies, I entered my attempt at the code above. Also @chris the "Chr(10)" thing worked perfectly! I only need help with preventing my code from crashing (see my last 2 sentences of my updated question) – Bijan Sanchez Jun 20 '22 at 19:23
  • @BijanSanchez that's really a new question, what you originally asked is answered by the dup. That said, the new Q would also be a dup. Two things: 1) get `lenrows` from column B, if the last entry in A is merged you'll get the wrong row. 2) when deleting rows in a loop, loop from bottom to top. `For i = lenrows To 2 Step -1`. You'll need other changes to cope with that – chris neilsen Jun 20 '22 at 19:34
  • @chrisneilsen wow excellent idea to loop from bottom to top, that worked perfectly. Sorry for the dups, but thank you for your help! – Bijan Sanchez Jun 20 '22 at 19:38

1 Answers1

1

The "alt+enter" is represented by the ascii code 10. So just add the character Chr(10) when you concatenate the values.

Example:

Sub MergeWithAltEnter(r As Range)

    merged = r(1)
    For i = 2 To r.Count
        merged = merged + Chr(10) + r(i)
    Next
    
    r(1) = merged
    r.Merge
    
End Sub
Victor Silva
  • 11
  • 1
  • I'm not sure how to run this. When I enter it in a new module, it asks for a macro name when I click run, but doesn't run it. Also I cannot assign it to a button. Does it have to do with the "(r as range)"? – Bijan Sanchez Jun 20 '22 at 19:32