CODEWITHSUNDEEP
vbaexcel
odoc
odoc

Reputation: 79

Run a Macro every time sheet is changed

i'm still fairly new to macros, i've got a bit of code i need to run on a sheet every time it gets updated, changed, or whatever.

Here is the code I need to run: How can i do this?

Sub UnMergeFill()

    Dim cell As Range, joinedCells As Range

    For Each cell In ThisWorkbook.ActiveSheet.UsedRange
        If cell.MergeCells Then
            Set joinedCells = cell.MergeArea
            cell.MergeCells = False
            joinedCells.Value = cell.Value
        End If
    Next

End Sub

Upvotes: 2

Views: 8522

Answers (2)

user4039065
user4039065

Reputation:

You can boost the efficiency of your macro by locating the merged cells to process rather than looping through every cell in the Worksheet.UsedRange property and examining it for the Range.MergeCells Property.

Within the worksheet's conventional Range.Find method, there is an option to look for formatting. On this sub-dialog's Alignment tab, you'll find the option to locate Merged cells.

        Merged Cells Find

This can be incorporated into your VBA sub procedure using the Range.Find method and the Application object's .FindFormat property.

Your sub procedure using FindFormat:

Sub UnMergeFill(Optional ws As Worksheet)
    If ws Is Nothing Then Set ws = ActiveSheet
    Dim fndMrg As Range, joinedCells As Range
    
    Application.FindFormat.MergeCells = True
    With ws
        On Error Resume Next
        Set fndMrg = .Cells.Find(What:=vbNullString, SearchFormat:=True)
        Do While Not fndMrg Is Nothing
            Set joinedCells = fndMrg.MergeArea
            fndMrg.MergeCells = False
            'fndMrg.UnMerge   '???
            joinedCells.Value = fndMrg.Value
            Set fndMrg = .Cells.Find(What:=vbNullString, SearchFormat:=True)
        Loop
    End With
    Application.FindFormat.MergeCells = False

End Sub

Slightly revised Worksheet_Change event macro with more environment shutdown during processing.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo bm_Safe_Exit
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Call UnMergeFill(Target.Parent)
    
bm_Safe_Exit:
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I've opted to specify the worksheet to be processed rather than rely on the ActiveSheet property. There is the possibility that the Worksheet_Change could be initiated by an outside process when it is NOT the active sheet.

In short, opt for bulk operations whenever possible and avoid looping whenever you can. This is not blinding fast but it should be substantially quicker than looping through the cells.

Upvotes: 5

Excel Hero
Excel Hero

Reputation: 14764

In the code module for that particular worksheet, just add this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    UnMergeFill
    Application.EnableEvents = True
End Sub

Upvotes: 4

Related Questions