In my workbooks 'Service Providers' start up page 'Start Page' the range F9:F38 is…
- User
In my workbooks 'Service Providers' start up page 'Start Page' the range F9:F38 is usually empty. However, if I change the value from a list selection in F3, the range F9:F38 is then popolated with data. I am wondering if using VBA code, the following can be achieved. When the sheet 'Start Page' is activated, the range F9:F38 can be merged into one. But when I change the value from a list selection in F3, the range F9:F38 can be unmerged with row heights of 14.
- Assistant
Yes, you can achieve this using VBA code. Here's an example of how you can write the code:
Private Sub Worksheet_Activate() Me.Range("F9:F38").Merge End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$F$3" Then Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubAdd this code to the VBA editor by pressing
Alt + F11, insert a new module, and paste the code into the module. Save your workbook as a macro-enabled file (.xlsm) and whenever the sheet 'Start Page' is activated, the range F9:F38 will be merged into one cell. If the value in cell F3 changes, the range F9:F38 will be unmerged with a row height of 14.Make sure to test the code on a copy of your workbook before applying it to your original workbook.
- User
Application defined or object defined error on the line Me.Range("F9:F38").Merge
- Assistant
Apologies for the error. Let's modify the code to check if the range is already merged before attempting to merge it again. Here's the updated code:
Private Sub Worksheet_Activate() If Not Me.Range("F9").MergeCells Then Me.Range("F9:F38").Merge End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$F$3" Then Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubThis code checks if the range F9:F38 is already merged before attempting to merge it again on the
Worksheet_Activateevent. This should help prevent the "Application defined or object defined error" you encountered.Please try using this updated code and let me know if it resolves the issue.
- User
Same error on the line: Me.Range("F9:F38").Merge
- Assistant
I apologize for the oversight. Let's try a different approach to merge the cells in range F9:F38 without encountering the error. We can loop through each cell in the range individually and merge them one by one. Here's the updated code:
Private Sub Worksheet_Activate() Dim cell As Range For Each cell In Me.Range("F9:F38") If Not cell.MergeCells Then cell.Merge End If Next cell End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubThis code loops through each cell in the range F9:F38 and checks if the cell is already merged before merging it. We also updated the worksheet change event to unmerge the range F9:F38 and set the row height to 14 when the value in cell F3 changes.
Please try using this modified code and let me know if it resolves the error.
- User
same error but now on 'cell.Merge'
- Assistant
I see, if merging the cells individually is also causing an error, we can try a different approach. We can temporarily copy the merged content to a single cell, merge the entire range, and then paste the content back. Here's the updated code:
Private Sub Worksheet_Activate() Dim mergedContent As String ' Copy the merged content to a single cell mergedContent = Me.Range("F9:F38").Cells(1, 1).Value ' Unmerge the range Me.Range("F9:F38").UnMerge ' Merge the entire range Me.Range("F9:F38").Merge ' Paste the merged content back to the first cell Me.Range("F9").Value = mergedContent End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubThis code should work by temporarily copying the merged content to a single cell, unmerging the range, merging the entire range, and then pasting the content back to the first cell. This workaround should help avoid the error you encountered when trying to merge the cells individually.
Please give this updated code a try and let me know if it resolves the issue.
- User
does it matter that there will be no content in the range
- Assistant
If there won't be any content in the range F9:F38 when you merge the cells, it should not cause any issues. The cells will be merged without content, and you can always add content to the merged cell afterward.
Here's an updated version of the code that considers an empty range F9:F38:
Private Sub Worksheet_Activate() Dim mergedContent As String ' Check if the range F9:F38 is not empty If Application.WorksheetFunction.CountA(Me.Range("F9:F38")) > 0 Then ' Copy the merged content to a single cell mergedContent = Me.Range("F9").Value ' Unmerge the range Me.Range("F9:F38").UnMerge ' Merge the entire range Me.Range("F9:F38").Merge ' Paste the merged content back to the first cell Me.Range("F9").Value = mergedContent End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubThis code first checks if the range F9:F38 is not empty before attempting to copy the content to a single cell. If the range is empty, it will still merge the cells without content. The rest of the code remains the same.
Please try this updated code and see if it meets your requirements. Let me know if you encounter any issues or if you need further assistance.
- User
Did not get any errors, but the range F9:F38 was not merged
- Assistant
I apologize for the oversight. Let's modify the code to ensure that the range F9:F38 is merged, even if it is empty:
Private Sub Worksheet_Activate() ' Merge the entire range F9:F38 Me.Range("F9:F38").Merge End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then ' Unmerge the range F9:F38 and set row height to 14 Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubBy simplifying the code in this way, the range F9:F38 will be merged when the worksheet is activated, regardless of whether it contains content. When the value in cell F3 changes, the range will be unmerged and the row height will be set to 14.
Please try using this updated code and let me know if it successfully merges the range F9:F38 when the sheet is activated.
- User
Me.Range(“F9:F38”).Merge, worked. But can I get the code to check if it is currently merged or not. If it is already merge then there is no need to merge the cells. If it is not merged then continue with the merge.
- Assistant
Certainly! To check if a range of cells is already merged before merging it, you can use the
MergeCellsproperty of the first cell in the range. Here's how you can modify the code to check if the range F9:F38 is already merged before attempting to merge it:Private Sub Worksheet_Activate() ' Check if the range F9:F38 is not already merged If Not Me.Range("F9").MergeCells Then ' Merge the entire range F9:F38 Me.Range("F9:F38").Merge End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then ' Unmerge the range F9:F38 and set row height to 14 Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubIn this updated code, we first check if cell F9 is merged (
Me.Range("F9").MergeCells). If it is not already merged, then we go ahead and merge the entire range F9:F38. This check ensures that we only merge the cells if they are not already merged, as per your requirement.Please give this updated code a try and let me know if it successfully checks for existing merge before proceeding to merge the range F9:F38.
- User
For this part of the code, can it first clear the contents in the merge range before unmerging
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range(“F3”)) Is Nothing Then ’ Unmerge the range F9:F38 and set row height to 14 Me.Range(“F9:F38”).UnMerge Me.Range(“F9:F38”).RowHeight = 14 End If End Sub
- Assistant
Certainly! You can clear the contents of the merged range before unmerging it. Here's the updated code with the additional step to clear the contents:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F3")) Is Nothing Then ' Clear the contents of the merged range F9:F38 Me.Range("F9:F38").ClearContents ' Unmerge the range F9:F38 and set row height to 14 Me.Range("F9:F38").UnMerge Me.Range("F9:F38").RowHeight = 14 End If End SubIn this modified code, before unmerging the range F9:F38 and setting the row height to 14, the code first clears the contents of the merged range using
Me.Range("F9:F38").ClearContents.Please try using this updated code and let me know if it works as per your requirements. Feel free to reach out if you have any more questions or need further assistance.