Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

In my workbooks 'Service Providers' start up page 'Start Page' the range F9:F38 is…

  1. 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.

  2. 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 Sub
    

    Add 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.

  3. User

    Application defined or object defined error on the line Me.Range("F9:F38").Merge

  4. 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 Sub
    

    This code checks if the range F9:F38 is already merged before attempting to merge it again on the Worksheet_Activate event. 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.

  5. User

    Same error on the line: Me.Range("F9:F38").Merge

  6. 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 Sub
    

    This 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.

  7. User

    same error but now on 'cell.Merge'

  8. 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 Sub
    

    This 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.

  9. User

    does it matter that there will be no content in the range

  10. 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 Sub
    

    This 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.

  11. User

    Did not get any errors, but the range F9:F38 was not merged

  12. 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 Sub
    

    By 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.

  13. 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.

  14. Assistant

    Certainly! To check if a range of cells is already merged before merging it, you can use the MergeCells property 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 Sub
    

    In 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.

  15. 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

  16. 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 Sub
    

    In 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.