Forum Discussion

Copper Contributor

Sep 23, 2023

Solved

about error in macros

i am running below code in excel but excel showing an error of 1) ca'nt execute code in break mode and 2) compile error : sub and function not defined . How can i fix these plz help: code is : S...

Macros and VBA

OliverScheurich
Sep 23, 2023
ajinkyabedse
1) Can't execute code in break mode | Microsoft Learn Here is information on how to terminate break mode.
2) Below code returns the intended result in my sheet.

Sub MergeRowsWithLineBreak()
'Select the range of cells that you want to merge.
Dim rng As Range
Dim cell As Range
Set rng = Selection
'Create a new variable to store the merged data.
Dim mergedData As String
'Iterate through the selected range and merge the data into a single string, with line breaks in between.
For Each cell In rng
mergedData = mergedData & cell.Value & Chr(10)
Next cell
'Remove the trailing line break from the merged data.
mergedData = Left(mergedData, Len(mergedData) - 1)
'Paste the merged data into the first cell in the selected range.
rng.Cells(1).Value = mergedData
End Sub

OliverScheurich

Gold Contributor

Sep 23, 2023

ajinkyabedse

1) Can't execute code in break mode | Microsoft Learn Here is information on how to terminate break mode.

2) Below code returns the intended result in my sheet.

Sub MergeRowsWithLineBreak()

'Select the range of cells that you want to merge.
Dim rng As Range
Dim cell As Range

Set rng = Selection

'Create a new variable to store the merged data.
Dim mergedData As String

'Iterate through the selected range and merge the data into a single string, with line breaks in between.
For Each cell In rng
mergedData = mergedData & cell.Value & Chr(10)
Next cell

'Remove the trailing line break from the merged data.
mergedData = Left(mergedData, Len(mergedData) - 1)

'Paste the merged data into the first cell in the selected range.
rng.Cells(1).Value = mergedData

End Sub

ajinkyabedse

Copper Contributor

Sep 24, 2023

thank you so much its working. but other cells data which i selected are not deleted automatically . for Ex. i selected cells 1,2,3,4,5 and after running code all data merged to cell 1 and all others data must be deleted or these rows should be deleted. I want this . Can you provide me this code.
Thanking You !!

OliverScheurich
Gold Contributor
Sep 24, 2023
ajinkyabedse
You are welcome. This code returns the intended result in my sheet.

Sub MergeRowsWithLineBreak()
'Select the range of cells that you want to merge.
Dim rng As Range
Dim cell As Range
Set rng = Selection
'Create a new variable to store the merged data.
Dim mergedData As String
'Iterate through the selected range and merge the data into a single string, with line breaks in between.
For Each cell In rng
mergedData = mergedData & cell.Value & Chr(10)
cell.Clear
Next cell
'Remove the trailing line break from the merged data.
mergedData = Left(mergedData, Len(mergedData) - 1)
'Paste the merged data into the first cell in the selected range.
rng.Cells(1).Value = mergedData
End Sub