Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of Per Jessen Macro- paste/overwrite
Hi Per,
I have found an item within your last macro to me that I'd like to modify. What I'd like to do is to have a chr(10) inserted between each of the original elements. What I've tried thus far has resulted in either the placement of the chr(10) before, or after the text strings, but not in the middle of. How do I get it to place the chr(10) in between each line of the original contents? '--------------------------------------------- For cell = 1 To myRng.Cells.Count NewString = NewString & myRng.Cells(cell, 1).Value & " " myRng.Cells(cell, 1).ClearContents Next '----------------------------------- I've tried placing the chr(10) before the NewString, before the myRng.cells(cell,1).Value, and then before the two double quotes. If I place it before the NewString after the = sign, it inserts as many rows as are merged. Which makes sense. if I place it before the myRng.cells()..... if places a single row above the initial text, and then it does as I want-- a chr(10) at the end of each subsequent row. How can I get it to skip the insertion to the first row, and only do subsequent rows? Best. Thank you. '-------------------------------------------- Dim myRng ' As Range Dim NewString As String Sub steven() Set myRng = Nothing On Error Resume Next InputRange: Set myRng = Application.InputBox(prompt:="Select cells to concatenate", Type:=8) If myRng Is Nothing Then End If myRng.Cells.Count 5 Then msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try again", vbExclamation, "Regards, Per Jessen") GoTo InputRange End If For cell = 1 To myRng.Cells.Count NewString = NewString & myRng.Cells(cell, 1).Value & " " myRng.Cells(cell, 1).ClearContents Next myRng.Cells(1, 1) = NewString myRng.MergeCells = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent PivotTable Overwrite Warning and Disallow Overwrite | Excel Programming | |||
How to paste blanks only without overwrite exist cells in excel | Excel Discussion (Misc queries) | |||
Sub to copy a target sheet from slaves from and paste/overwrite into master | Excel Programming | |||
Macro modification | Excel Programming | |||
Need Macro Modification | Excel Programming |