Excel Unmerge Cell
- Thread starter rohit.x07
- Start date
-
- Tags
- excel unmerge
To UnMerge a range of cells, you have to add a small piece of code. Here are the steps to achieve un-merging of cells.
Take a copy of the existing Excel VBO and name it myExcel VBO. Open the myExcel VBO and right click on 'Select' object and choose 'Duplicate' from mouse menu. On Name new page pop-up, give a name as Select - UnMerge and click OK. Now, this will create a new object, Select - UnMerge in myExcel VBO. Do not forget to publish the new object Select - UnMerge.
On the 'Select - UnMerge' double-click on the 'Select Cells' Code Stage and change the name to 'Select Cells - Unmerge' (to avoid duplicate name error). Go to Code tab and add the below line after range.Select() line
Click on Ok and Save the myExcel VBO object.
Now, you are ready with the code changes to unmerge the cells.
In your Process add an action, Select - UnMerge from myExcel VBO with inputs- Handle, Workbook, worksheet & Reference. For input Reference, pass the merge cells range, example "A1:A3". On executing the action, it will un-merge the cells.
Note: Entire Process should call myExcel VBO instead of Excel VBO, else you will run in to exceptions if you try to use only one action from myExcel VBO and have all other actions calling Excel VBO.
Post back, how it goes.
Take a copy of the existing Excel VBO and name it myExcel VBO. Open the myExcel VBO and right click on 'Select' object and choose 'Duplicate' from mouse menu. On Name new page pop-up, give a name as Select - UnMerge and click OK. Now, this will create a new object, Select - UnMerge in myExcel VBO. Do not forget to publish the new object Select - UnMerge.
On the 'Select - UnMerge' double-click on the 'Select Cells' Code Stage and change the name to 'Select Cells - Unmerge' (to avoid duplicate name error). Go to Code tab and add the below line after range.Select() line
sheet.Range(Reference).UnMerge()Click on Ok and Save the myExcel VBO object.
Now, you are ready with the code changes to unmerge the cells.
In your Process add an action, Select - UnMerge from myExcel VBO with inputs- Handle, Workbook, worksheet & Reference. For input Reference, pass the merge cells range, example "A1:A3". On executing the action, it will un-merge the cells.
Note: Entire Process should call myExcel VBO instead of Excel VBO, else you will run in to exceptions if you try to use only one action from myExcel VBO and have all other actions calling Excel VBO.
Post back, how it goes.
Thanks Sivagelli for complete and clear explanation and i hope it works for others too.
If we want to Unmerge the specific multiple rows in complete sheet in one complete action. How can we achieve.
Eg: in my excel there 5 merged cells from them i need to Unmerge only 2 cells and their ranges are different. How can we achieve by this in single action stage.
If we want to Unmerge the specific multiple rows in complete sheet in one complete action. How can we achieve.
Eg: in my excel there 5 merged cells from them i need to Unmerge only 2 cells and their ranges are different. How can we achieve by this in single action stage.
Alright! Use the below line of code inplace of above code to un-merge any number of merged cell ranges in an excel sheet.
Here is the complete code for object: Select -Unmerge. Here, pass the input for Reference as comma delimited values. For example, in a sheet you have 5 cell ranges merged and you want to unmerge only 2 cell ranges from them them, the input for Reference would be like "A1:A14,C23:G23".
Post back, how it goes!
sheet.cells.Unmerge()Here is the complete code for object: Select -Unmerge. Here, pass the input for Reference as comma delimited values. For example, in a sheet you have 5 cell ranges merged and you want to unmerge only 2 cell ranges from them them, the input for Reference would be like "A1:A14,C23:G23".
Code:
Dim wb, ws As Object
Dim excel, sheet As Object
Dim rArray() as String
Dim i as Integer
Try
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
rArray = Split(Reference, ",")
For i=LBound(rArray) to UBound(rArray)
sheet.Range(rArray(i)).UnMerge()
Next i
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
End TryPost back, how it goes!
edwards142
New Member
During this unmerging of cells in Excel all of sudden I started encountering Excel No Cells Were Found Error. After the encountrance of this error I am completely unable to use excel sheet and most of cells data goes blank. I don’t have any idea what I have done wrong. When I searched about this error luckily I have found this helpful post. this post contains compl;ete solution to fix Excel No Cells Were Found Error.You can also check it out….it’s really very helpful.