Automatic merge with cell below when cell fully populated wi

[rmtaylor]

Hi
I am attempting to adapt an existing sheet for work that is more user friendly for users with little experience on excel .
I am at the early stage of attempting to get my head round VBA (making Macros up and attempting to work out the code stage). I would be grateful if any one could give me any help or information on my query below, I have searched the board considerably but this query seems to be unsought in the past. Perhaps it is not possible.

When typing into a cells say D5 to D20 that are formatted to text only is it possible to arrange that when
or if the cell in question (any cell from say D5: D20) is fully populated with text that it automatically merges with the cell bellow to allow the user to continue typing without the need to merge .

The cells are for comments only adjacent right to a list of say 30 subjects. It is expected only a few of the subjects require to have comments added each time the sheet is completed therefor the cells provided ( to fit A4 sheet) are sometimes not large enough to take the length of the text and the comments sometimes continue in the cell below. Word wrapping cells is not possible as this row width does not allow enough room for two rows of text. I thought it would make it easier if the cell in question could be made to automatically merge with the cell below when if it was fully populated, with text, often paseted into the cell.

 

[jimboy]

Hi,

Insted of merging cell, why not wrap the text?

If that is better try this. Over the sheet name (i.e. "sheet1") right mouse and select "view code" then delete everything and paste this...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim currentcell
currentcell = ActiveCell.Address
Dim pcelll
pcelll = ActiveCell.Offset(-1, 0)

If Len(pcelll) > 8 Then 'change 8
ActiveCell.Offset(-1, 0).Range("A1").Select
With Selection
.WrapText = True
End With

Else

End If
Range(currentcell).Select
End Sub