VBA: faster & more efficient way to merge cells RRS feed

  • Question

  • Hi VBA experts!

    I have a workbook which could have any number of worksheets (at least 11 sheets with 110 rows and 10 columns in each), and have come up with this code by which a cell is merged with another cell above if it contains only 1 character. The code works fine, but it takes quite a while. Would there be any faster and more efficient way of merging cells across multiple worksheets? Help~~~

    Thank you!


    Sub Merge_1_Cells() 

    Application.DisplayAlerts = False

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

        .EnableEvents = False

        .DisplayAlerts = False

        .Cursor = xlWait

        .EnableCancelKey = xlErrorHandler

    End With

    Dim x As Integer 'row

    Dim y As Integer 'column

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets


        For x = 5 To ws.UsedRange.rows.Count

            For y = 2 To ws.UsedRange.Columns.Count

                If Len(ws.Cells(x, y)) = 1 Then ws.Range(Cells(x - 1, y).Address & ":" & Cells(x, y).Address).Merge

            Next y

        Next x


    Application.DisplayAlerts = True

    With Application

        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

        .EnableEvents = True

        .DisplayAlerts = True

        .Cursor = xlDefault

        .StatusBar = False

        .EnableCancelKey = xlInterrupt

    End With

    End Sub


    Tuesday, November 20, 2018 1:43 AM

All replies