Forum Discussion
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 :
Sub MergeRowsWithLineBreak()
'Select the range of cells that you want to merge.
Dim rng 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 & CHAR(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
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 RangeSet 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 = mergedDataEnd Sub
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 RangeSet 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 = mergedDataEnd Sub
- 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 !!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 RangeSet 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 = mergedDataEnd Sub
OliverScheurich Can you help me with below task ?
As you suggested macro and that works great. but I have big data which consumes lot of time. So, I decided to go with the productive method-
I have data of mcqs in column A only of excel . where cell 1 contains mcq which starts from numerical. mcq followed by exam name which is in cell 2, exam name followed by options 1,2,3,4 where options starts with 1. , 2. , 3. , 4. , respectively. I want to combine rows of mcq and exam name with line breaks. (which is same macro which u gave me previously) . i want this process automatically. (Note that some mcqs are not in single cell, they are in consecutive cells) So, can you create a macro that can Combine my mcq cells and exam name cell. (Suggestion - You can use to combine rows between the previous mcqs's fourth option which starts from 4. and the current mcq's first option which starts from 1.)
Attaching file of my task.
Hello i'm glad that the macro works as intended. Actually it's your macro and i've only made slight changes. Unfortunately i'm not sure i understand which result you want to achive in the "SAMPLE - Copy" file. Perhaps another contributor of the community can help you with this. In general i'd recommend not working with merged cells because they are known to cause problems.
