CODEWITHSUNDEEP
vbaexcelrows
Hüseyin Dursun
Hüseyin Dursun

Reputation: 600

Excel VBA Macro Code Inserting Unlimited Rows Without Stopping

The below code is automatically runs when a cell in a specified column changes and if it is not empty.

Sub mergeCells()
    Dim num As Integer
    Dim countmerged As Integer
    If IsEmpty(ActiveCell.Value) Then
        Exit Sub
    Else
        countmerged = -1
        If ActiveCell.Offset(-1, 0).mergeCells Then
            countmerged = ActiveCell.Offset(-1, 0).MergeArea.Cells.Count * -1
        End If
        num = ActiveCell.Offset(countmerged, -1).Value
        If ActiveCell.Offset(countmerged, 0).Value = ActiveCell.Value Then
            ActiveCell.ClearContents
            ActiveCell.Offset(0, 1).ClearContents
            ActiveCell.Offset(0, 37).ClearContents
            ActiveCell.Offset(0, 36).ClearContents
            ActiveCell.Offset(0, -1).ClearContents
            ActiveCell.Offset(1, 0).EntireRow.Insert
            ActiveCell.Offset(2, 0).EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
            Range(ActiveCell.Offset(countmerged, 37), ActiveCell.Offset(0, 37)).Merge
            Range(ActiveCell.Offset(countmerged, 36), ActiveCell.Offset(0, 36)).Merge
            Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(countmerged, 1)).Merge
            Range(ActiveCell.Offset(countmerged, -1), ActiveCell.Offset(0, -1)).Merge
            Range(ActiveCell, ActiveCell.Offset(countmerged, 0)).Merge
            ActiveCell.Offset(1, -1).Value = num + 1
            ActiveCell.Offset(2, -1).Value = num + 2
        Else
            ActiveCell.Offset(1, 0).EntireRow.Insert
            ActiveCell.Offset(2, 0).EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
            Selection.Offset(1, -1).Value = num + 2
            Selection.Offset(2, -1).Value = num + 3
        End If
    End If
End Sub

if the value is the same value with the above cell, they are being merged and another row with the same formulas is inserted. This works without problem.

But if the value is not the same as the above cell, only a row must be inserted with the same formulas but it adds rows without stopping.

Upvotes: 2

Views: 570

Answers (1)

gtwebb
gtwebb

Reputation: 3011

I don't think you are showing us the important part of the code (that sets this one off).

I would try disabling events since the macro is likely changing a cell and seeing that a cell is changed (inserted, whatever) starting your event again.

Try adding these at the beginning and ending of your macro.

Application.EnableEvents = False  


Application.EnableEvents = True

Upvotes: 2

Related Questions