Automatic merge with cell below when cell fully populated wi

rmtaylor

Board Regular
Joined
Feb 17, 2002
Messages
155
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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
 

Forum statistics

Threads
1,143,917
Messages
5,721,529
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top